| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding Text from seperate columns
I have delimited a column of data that had a list of peoples names including their title to several columns. Unfortunately the format was not uniform in the column so some included their first and middle initials and they either separated it by a space or a period - so some people had put: eg. Mr John Smith Mr JS Smith Mr J B Smith Mr J.C Smith So by delimiting by spaces it meant most spanned 3 columns while others spanned 4 columns. What I want to do is for those that spanned 4 (because they put first initial/name and then [separated by a space] put their middle name or initial) is to add them together. eg. Mr | John | Smith | Mr | JS | Smith | Mr | J | B | Smith| <--- spans 4 Mr | J.C | Smith | With regards to my example for Mr J B Smith, I need a way to add the two columns so that the "J" and the "B" are added into a single column so its like "JB" Mr | John | Smith | Mr | JS | Smith | Mr | JB | Smith | <--- spans 3 Mr | J.C | Smith | Any help with this would be much appreciated! Regards, PC -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=541520 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Text from seperate columns
Assuming your example:
Mr | John | Smith | Mr | JS | Smith | Mr | J | B | Smith| Mr | J.C | Smith | starts at cell A1, then: cell F1 use the formula =A1, cell G1 use the formula =IF(D1="",B1,CONCATENATE(B1,C1)) cell H1 use the formula =IF(D1="",C1,D1) HTH Ian "paperclip" wrote: I have delimited a column of data that had a list of peoples names including their title to several columns. Unfortunately the format was not uniform in the column so some included their first and middle initials and they either separated it by a space or a period - so some people had put: eg. Mr John Smith Mr JS Smith Mr J B Smith Mr J.C Smith So by delimiting by spaces it meant most spanned 3 columns while others spanned 4 columns. What I want to do is for those that spanned 4 (because they put first initial/name and then [separated by a space] put their middle name or initial) is to add them together. eg. Mr | John | Smith | Mr | JS | Smith | Mr | J | B | Smith| <--- spans 4 Mr | J.C | Smith | With regards to my example for Mr J B Smith, I need a way to add the two columns so that the "J" and the "B" are added into a single column so its like "JB" Mr | John | Smith | Mr | JS | Smith | Mr | JB | Smith | <--- spans 3 Mr | J.C | Smith | Any help with this would be much appreciated! Regards, PC -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=541520 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding Text from seperate columns
OMG your a freakin' genius - i thought it impossible! :-) Shows how much I know!! -- paperclip ------------------------------------------------------------------------ paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219 View this thread: http://www.excelforum.com/showthread...hreadid=541520 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TEXT TO COLUMNS WITH LEADING ZEROS | Excel Discussion (Misc queries) | |||
| Totaling columns of text in Excel | Excel Worksheet Functions | |||
| macro to split text in columns | Excel Discussion (Misc queries) | |||
| Text to Columns - moves text up | Excel Discussion (Misc queries) | |||
| How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions | |||
Linear Mode
