Pau Lo
asked on
excel name format
I have a column of data whereby the name is included in a single string of text, in format:
surname, title forename initial (note the comma after surname)
i.e
mouse, mr micky m
I need a way to spread these name elements out into 4 seperate columns. is this possible?
There can be 4 elements to each name, i.e. if the user has more than one middle name/initial, so it isnt restricted to 4 each time.
surname, title forename initial (note the comma after surname)
i.e
mouse, mr micky m
I need a way to spread these name elements out into 4 seperate columns. is this possible?
There can be 4 elements to each name, i.e. if the user has more than one middle name/initial, so it isnt restricted to 4 each time.
Can you use text to column tool under data tab?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How would text to columns work though as there are 2 seperate delimeters, i.e. , and space, or do you recommend first doing the comma, then doing the space?
as i have attached in my earlier post with attachment, this solution is so easy and you do not need different formulas, it can be done with one formula.
assuming your data starts A2 then put the formula =TRIM(MID(SUBSTITUTE($A2,"
assuming your data starts A2 then put the formula =TRIM(MID(SUBSTITUTE($A2,"
pma111
why dont you use a single formula to take care of all of it.
why dont you use a single formula to take care of all of it.
I would just use space as the delimiter, then do a find (ctrl+f) replace (comma with nothing) on comma on the Surname after.
if you insist in using the text 2 column then use only space as delimiter
ExcelAddict:
That is a really nice formula could come in handy in all sorts of cases.
Could you explain how it works in a little detail.
Thanks,
Steve.
That is a really nice formula could come in handy in all sorts of cases.
Could you explain how it works in a little detail.
Thanks,
Steve.
Steve:
easy the , let me break it down in to the pieces
first the Substitude formula replaces the each single space to 99 time more meaning the each space will be 99 spaces.
then Column A as you know return 1 then to extract the first name from the string 1 * 99 return 99 then 99-98 is again 1 so the mid function will take will start from the first char of the string up to 99 which means becuase we already replaces the single space with 99 speaces so mid function will take the first name along with its trailing spaces, so then the trim function removes all the trailing spaces and only remains is the first name.
now the trick is the extraction of second and third name in the string, now the column A moving to Column B changes the value to 2 then 2*99 is 198 then 198-98 it is 100 and the starting position of MID function is 100 which is now beyong the lenght of first name and it will jump to the second name and then the same procedure of MID function 99 char and trim removing the spaces will do the job.
for the third name in the string the column will be COlumn C and then value is 3, 3 will be 3*99 = 297 - 98 =199 the mid function starting position will be 199 which is beyond the position of the second name.
and so on.
hope my explanation makes sense
=TRIM(MID(SUBSTITUTE($A2,"
easy the , let me break it down in to the pieces
first the Substitude formula replaces the each single space to 99 time more meaning the each space will be 99 spaces.
then Column A as you know return 1 then to extract the first name from the string 1 * 99 return 99 then 99-98 is again 1 so the mid function will take will start from the first char of the string up to 99 which means becuase we already replaces the single space with 99 speaces so mid function will take the first name along with its trailing spaces, so then the trim function removes all the trailing spaces and only remains is the first name.
now the trick is the extraction of second and third name in the string, now the column A moving to Column B changes the value to 2 then 2*99 is 198 then 198-98 it is 100 and the starting position of MID function is 100 which is now beyong the lenght of first name and it will jump to the second name and then the same procedure of MID function 99 char and trim removing the spaces will do the job.
for the third name in the string the column will be COlumn C and then value is 3, 3 will be 3*99 = 297 - 98 =199 the mid function starting position will be 199 which is beyond the position of the second name.
and so on.
hope my explanation makes sense
=TRIM(MID(SUBSTITUTE($A2,"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ExcelAddict... thanks for the explanation. As you say, simple once explained.
My only question would be why use COLUMNS($A$50000:A50000) rather than COLUMN(A:A)?
Not a huge difference, but just wondering if there is a reason?
TA
Steve.
My only question would be why use COLUMNS($A$50000:A50000) rather than COLUMN(A:A)?
Not a huge difference, but just wondering if there is a reason?
TA
Steve.