Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Problem
Hi,
I am using Office 2K7 and facing a problem in splitting the text into different columns. I have lastnames and firstnames together in a cell and there is no such delimiter between them which can be identified by a single way that the last name starts with a Upper letter only. e.g. CandyRachel . I need to separate it into two different columns as 'Candy' and 'Rachel'. I think this is possible using Char function but not able to find out the way. Any help will be appreciated.. Thanks in advance.. Miki |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Problem
Assuming your data in A1 and down
In C1: =MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))=65)* (CODE(MID(A1,ROW($2:$99),1))<90),)+1,99) ctrl+shift+enter, not just enter In B1: =SUBSTITUTE(A1,C1,) Just preess enter "Miki" wrote: Hi, I am using Office 2K7 and facing a problem in splitting the text into different columns. I have lastnames and firstnames together in a cell and there is no such delimiter between them which can be identified by a single way that the last name starts with a Upper letter only. e.g. CandyRachel . I need to separate it into two different columns as 'Candy' and 'Rachel'. I think this is possible using Char function but not able to find out the way. Any help will be appreciated.. Thanks in advance.. Miki |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Problem
Try one of these.
Create this defined name Goto the Formulas tabDefined namesDefine name Name: Letters Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" OK out Note that the underscores are intentional and the letters need to be in uppercase. A2 = CandyRachel Here's the short formula version**. Entered in C2 to extract the last name. =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20) Entered in B2 to extract the first name: =SUBSTITUTE(A2,C2,"") Here's the long formula version** to extract the last name: =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20) ** the difference in the short and long formulas is that the long version is robust against row insertions in certain situations. If you know that you will never need to insert new rows at the top of the sheet then use the short version. When using an expression like: ROW(A$2:A$50) This expression is vulnerable to row insertions which could lead the formula to fail. For example, if you inserted a new row 1 then that expression will change to reflect the row insertion. Instead of: ROW(A$2:A$50) It will change to: ROW(A$3:A$51) We're using this expression in the formula to define the starting point at which to look for the an uppercase letter. So, inserting new rows could shift that starting point and lead the formula to fail. For this reason I would use the long formula which is robust against (accounts for) such row insertions. The downside of using the long version is that it's a bit longer and it uses the volatile function INDIRECT which means that this (these) formulas will recalculate *every* time a calculation takes place. If you have "lots" of volatile functions it could possibly slow things down. -- Biff Microsoft Excel MVP "Miki" wrote in message ... Hi, I am using Office 2K7 and facing a problem in splitting the text into different columns. I have lastnames and firstnames together in a cell and there is no such delimiter between them which can be identified by a single way that the last name starts with a Upper letter only. e.g. CandyRachel . I need to separate it into two different columns as 'Candy' and 'Rachel'. I think this is possible using Char function but not able to find out the way. Any help will be appreciated.. Thanks in advance.. Miki |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Problem
=MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20)
Found a bug in that formula when the last name starts with the letter A. To correct it change the defined named string from: ="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" To: ="_ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these. Create this defined name Goto the Formulas tabDefined namesDefine name Name: Letters Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" OK out Note that the underscores are intentional and the letters need to be in uppercase. A2 = CandyRachel Here's the short formula version**. Entered in C2 to extract the last name. =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20) Entered in B2 to extract the first name: =SUBSTITUTE(A2,C2,"") Here's the long formula version** to extract the last name: =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20) ** the difference in the short and long formulas is that the long version is robust against row insertions in certain situations. If you know that you will never need to insert new rows at the top of the sheet then use the short version. When using an expression like: ROW(A$2:A$50) This expression is vulnerable to row insertions which could lead the formula to fail. For example, if you inserted a new row 1 then that expression will change to reflect the row insertion. Instead of: ROW(A$2:A$50) It will change to: ROW(A$3:A$51) We're using this expression in the formula to define the starting point at which to look for the an uppercase letter. So, inserting new rows could shift that starting point and lead the formula to fail. For this reason I would use the long formula which is robust against (accounts for) such row insertions. The downside of using the long version is that it's a bit longer and it uses the volatile function INDIRECT which means that this (these) formulas will recalculate *every* time a calculation takes place. If you have "lots" of volatile functions it could possibly slow things down. -- Biff Microsoft Excel MVP "Miki" wrote in message ... Hi, I am using Office 2K7 and facing a problem in splitting the text into different columns. I have lastnames and firstnames together in a cell and there is no such delimiter between them which can be identified by a single way that the last name starts with a Upper letter only. e.g. CandyRachel . I need to separate it into two different columns as 'Candy' and 'Rachel'. I think this is possible using Char function but not able to find out the way. Any help will be appreciated.. Thanks in advance.. Miki |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Problem
Here's another one (2 versions) that *does not* need the named string.
These are array formulas**. =MID(A2,MATCH(1,--(ABS(CODE(MID(A2,ROW(A$2:A$50),1))-77.5)<13),0)+1,20) =MID(A2,MATCH(1,--(ABS(CODE(MID(A2,ROW(INDIRECT("2:50")),1))-77.5)<13),0)+1,20) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20) Found a bug in that formula when the last name starts with the letter A. To correct it change the defined named string from: ="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" To: ="_ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try one of these. Create this defined name Goto the Formulas tabDefined namesDefine name Name: Letters Refers to: ="ABCDEFGHIJKLMNOPQRSTUVWX_Y_Z" OK out Note that the underscores are intentional and the letters need to be in uppercase. A2 = CandyRachel Here's the short formula version**. Entered in C2 to extract the last name. =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(A$2:A$50),1),Letters),ROW(A$2:A$50 )),20) Entered in B2 to extract the first name: =SUBSTITUTE(A2,C2,"") Here's the long formula version** to extract the last name: =MID(A2,LOOKUP(0.9,1/FIND(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1),Letters ),ROW(INDIRECT("2:"&LEN(A2)))),20) ** the difference in the short and long formulas is that the long version is robust against row insertions in certain situations. If you know that you will never need to insert new rows at the top of the sheet then use the short version. When using an expression like: ROW(A$2:A$50) This expression is vulnerable to row insertions which could lead the formula to fail. For example, if you inserted a new row 1 then that expression will change to reflect the row insertion. Instead of: ROW(A$2:A$50) It will change to: ROW(A$3:A$51) We're using this expression in the formula to define the starting point at which to look for the an uppercase letter. So, inserting new rows could shift that starting point and lead the formula to fail. For this reason I would use the long formula which is robust against (accounts for) such row insertions. The downside of using the long version is that it's a bit longer and it uses the volatile function INDIRECT which means that this (these) formulas will recalculate *every* time a calculation takes place. If you have "lots" of volatile functions it could possibly slow things down. -- Biff Microsoft Excel MVP "Miki" wrote in message ... Hi, I am using Office 2K7 and facing a problem in splitting the text into different columns. I have lastnames and firstnames together in a cell and there is no such delimiter between them which can be identified by a single way that the last name starts with a Upper letter only. e.g. CandyRachel . I need to separate it into two different columns as 'Candy' and 'Rachel'. I think this is possible using Char function but not able to find out the way. Any help will be appreciated.. Thanks in advance.. Miki |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with Data|Text to Columns | New Users to Excel | |||
Text to Columns Problem | Excel Discussion (Misc queries) | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
Text to columns macro problem | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions |