(ii) Then select the cell with a month (i.e., B2) and put a comma (,) to close the text3. Use a delimiter slash (/) in the double quotes and put a comma (,) after that to close the text2. (i) First select the cell with a day (i.e., C2) in the CONCATENATEfunction and put a comma (,) to close the text1. We should take care of joining the criteria.As a result, the CONCATENATE syntax appears with open parenthesis. Select CONCATENATE from the drop-down list and press the РІР‚В TabРІР‚В key on the keyboard simultaneously. Excel suggests different functions starting with РІР‚В CONРІР‚В. In Step 1, we use the CONCATENATE function to join the dates, months and years.Р”Р„pply an equality РІР‚В = РІР‚В sign in cell D2 and then type a few characters of РІР‚В conРІР‚В¦.(01) TO USE CONCATENATE() FUNCTION JOINING THE DAYS, MONTHS & YEARS WITH DELIMITER SLASH РІР‚В/' (03) Finally, using the РІР‚ВConvert Text to Columns wizard' to convert invalid date formats to valid date formats. (02) To use РІР‚ВPaste Special' to convert the formulas into values. (01) To use CONCATENATE() function joining the DAYS, MONTHS and YEARS with delimiter slash РІР‚В/'. It is a very common problem found in Data preparation how making a valid date format by joining the day, month and year respectively. Р’С›Р’С›Р’С›Р’С›РІвЂ“В Р’В EXAMPLE 5: JOIN DAYS, MONTHS & YEARS TO FORM VALID DATE FORMATS (DELIMITER IS SLASH РІР‚Сљ/РІР‚Сњ) This would instantly split the days, months and years into different columns. Select the data range, i.e., A2:A14 РІС›Р„ Go to the РІР‚В DataРІР‚В tab РІС›Р„ Click on РІР‚В Text to ColumnsРІР‚В under the Data Tools section РІС›Р„ РІР‚В Convert Text to Columns WizardРІР‚В appears. РІвЂ“В Note: We had detail discussed on Excel shortcuts in separate two tutorials, suggested you read these tutorials:Р”РЃ0+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard ShortcutsР”В©0+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts | Select the data range, i.e., A2:A14 РІС›Р„ Press either Alt+D+E (sequentially press Alt, D, E)or Alt+A+E (sequentially press Alt, A, E) which will open the РІР‚ВConvert Text to Columns Wizard'. STEPS TO START TO CONVERT TEXT TO COLUMNS WIZARD РІвЂ“В Р’В EXAMPLE 10: CONVERT NUMBERS WITH TRAILING MINUS SIGN TO NEGATIVE NUMBERS.РІвЂ“В Р’В EXAMPLE 9: EXTRACT FIRST / LAST FEW CHARACTERS OF A FIXED WIDTH TEXT/STRING.Р’С›Р’С›Р’С›Р’С›РІвЂ“В Р’В EXAMPLE 6: SPLIT TEXT INTO YEARS, HOST NAMES & WINNER TEAMР’В (DELIMITERS ARE HYPHEN РІР‚Сљ-РІР‚Сњ & UNDERSCORE РІР‚Сљ_РІР‚Сњ).(03) USINGР’В THE РІР‚ВCONVERT TEXT TO COLUMNS WIZARDРІР‚В TO CONVERT INVALID DATE FORMATS TO VALID DATE FORMATS.

(02) TO USE РІР‚ВPASTE SPECIAL' TO CONVERT THE FORMULAS INTO VALUES.(01) TO USE CONCATENATE() FUNCTION JOINING THE DAYS, MONTHS & YEARS WITH DELIMITER SLASH РІР‚В/'.Р’С›Р’С›Р’С›Р’С›РІвЂ“В Р’В EXAMPLE 5: JOIN DAYS, MONTHS & YEARS TO FORM VALID DATE FORMATSР’В (DELIMITER IS SLASH РІР‚Сљ/РІР‚Сњ).Р’С›Р’С›Р’С›Р’С›РІвЂ“В Р’В EXAMPLE 4: SPLIT DATES IN THE DAYS, MONTHS & YEARS (DELIMITER IS HYPHEN РІР‚Сљ-РІР‚Сљ).Р’С›Р’С›Р’С›Р’С›РІвЂ“В Р’В EXAMPLE 3: CONVERT INVALID DATE FORMATS TO VALID DATE FORMATS.Р’С›Р’С›Р’С›Р’С›РІвЂ“В EXAMPLE 2: SPLIT DATE & TIME (DELIMITER IS SPACE РІР‚Сњ РІР‚Сљ).Р’С›Р’С›Р’С›Р’С›РІвЂ“В EXAMPLE 1: SPLIT FIRST NAME, LAST NAME (DELIMITER IS COMMA РІР‚В,') & COUNTRY NAME (DELIMITERS ARE AT THE RATE AND SPACE РІР‚В РІР‚В).EXAMPLES OF CONVERT TEXT TO COLUMNS WIZARD (2) Fixed Width: This feature splits the text having a fixed width (i.e., count of characters in the text remain same in each cell) which is being joined with spaces or hyphen or underscore or slash after some fixed width. (1) Delimited: This feature splits the text which is being joined by characters, Commas, Tabs, Spaces, Semicolons, Colons, or any other character such as a hyphen (-), underscore ( _), slash ( / ), etc. There are two separate features of Convert Text to Columns: In advanced Excel, this feature is basically used to convert РІР‚В invalid date formats to a valid date formatРІР‚В, convert РІР‚В number to textРІР‚В format and РІР‚В text to numberРІР‚В format, split a РІР‚В delimited text into multiple columnsРІР‚В, etc. Using Excel Text to Columns Wizard to separate DelimitedР’В text into the multiple columns.Р”В¬onvert Text to Columns can work for a single column only at a time, it does not work with multiple columns.

Excel split cells on commas