Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
Can you use a formula to split delimited data when number of characters in
each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
Look in the help index for FIND or SEARCH and then incorporate into a LEFT MID or RIGHT formula Don't know why you can't use text to columnsdelimitedspace?? -- Don Guillett SalesAid Software "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
In B1 enter:
=LEFT(A1,FIND(" ",A1)-1) In C1 enter: =RIGHT(A1,LEN(A1)-LEN(B1)-1) Select *both* B1 and C1, and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
I don't see why you say you can't use LEFT or RIGHT.
What's wrong with =LEFT(A7,FIND(" ",A7)-1) and =RIGHT(A7,LEN(A7)-FIND(" ",A7)) ? -- David Biddulph "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
For the left name =LEFT(A2,FIND(" ",A2)-1)
For the right name =MID(A2,FIND(" ",A2)+1,10) Does have some limitations, if there is a middle initial things will go nuts, but for the vanilla examples you offered this should work. If the right name is longer than 10 characters you will need to change the 10 to whatever. HTH Regards, Howard "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
"Don Guillett" wrote: Look in the help index for FIND or SEARCH and then incorporate into a LEFT MID or RIGHT formula Don't know why you can't use text to columnsdelimitedspace?? -- Don Guillett SalesAid Software "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! Thanks so much for your quick response! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
"Ragdyer" wrote: In B1 enter: =LEFT(A1,FIND(" ",A1)-1) In C1 enter: =RIGHT(A1,LEN(A1)-LEN(B1)-1) Select *both* B1 and C1, and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! Thanks so much for your quick response. The formulae you gave me work perfectly! I didn't know about the "FIND" function before. Thanks again. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
"David Biddulph" wrote: I don't see why you say you can't use LEFT or RIGHT. What's wrong with =LEFT(A7,FIND(" ",A7)-1) and =RIGHT(A7,LEN(A7)-FIND(" ",A7)) ? -- David Biddulph "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! Thanks for your qick response! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
"L. Howard Kittle" wrote: For the left name =LEFT(A2,FIND(" ",A2)-1) For the right name =MID(A2,FIND(" ",A2)+1,10) Does have some limitations, if there is a middle initial things will go nuts, but for the vanilla examples you offered this should work. If the right name is longer than 10 characters you will need to change the 10 to whatever. HTH Regards, Howard "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! Thanks for your quick response and the extra info! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Menu Text to columns
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NatChat" wrote in message ... "Ragdyer" wrote: In B1 enter: =LEFT(A1,FIND(" ",A1)-1) In C1 enter: =RIGHT(A1,LEN(A1)-LEN(B1)-1) Select *both* B1 and C1, and copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "NatChat" wrote in message ... Can you use a formula to split delimited data when number of characters in each cell varies (rather than using Text to columns from Data menu). For example: Column A 1 Smith Sue 2 Wilson Jane 3 Vincent Michael I want to split this data delimited by a space but can't use LEFT or RIGHT functions as number of characters varies in each cell. Please advise. Thank you for your help! Thanks so much for your quick response. The formulae you gave me work perfectly! I didn't know about the "FIND" function before. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions |