Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
I am using Excel 2003
I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? Thanks in advance! -- Cynthia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
You could use Data | Text to Column
OR If the numbers always 4 digits =--LEFT(A1,4) OR =--LEFT(A1,FIND(" ",A1)-1) for variable length numbers The double negation turns text to numbers best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cynthia" wrote in message ... I am using Excel 2003 I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? Thanks in advance! -- Cynthia |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
Assuming by "freestanding numbers" you mean those numbers at the end of the
address, here is one possible way to do what you asked. Put this formula in an unused (helper) column... =SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) and copy it down to the last row of data. Then select those cells in the helper column and press Ctrl+C (or click Edit/Copy) to Copy them. Then select the first cell of your original data and click Edit/PasteSpecial, select the Values from the options and click OK. Press Esc to unselect the original copy area and delete the helper column's data. You now have your original data with the last number delimited by a comma (although you may want to consider a different character in case there is a comma in the street address). Rick I am using Excel 2003 I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
Bernard,
when using: =--LEFT(A1,FIND(" ",A1)-1) for variable length numbers The double negation turns text to numbers I changed the direction from LEFT to RIGHT (where my data was) and had mixed results: 1550 Main St 477 477 2341 W Main 1 #VALUE! 1138 W Elm Ave D4 #VALUE! 41 Cleveland Avenue North 104 4 In every case, I have the apartment number remaining in the column of original data. I cannot use Text | Columns as I have no marker I can use to identify either a freestanding number, such as 3, or an apartment number with alphanumeric combination, such as D4. All the cells of original data are formatted as General. Please advise: 1. How to move these different apartment numbers accurately one column to the right 2. How to remove the apartment number from the column of original data Thank you, Cynthia -- "Bernard Liengme" wrote: You could use Data | Text to Column OR If the numbers always 4 digits =--LEFT(A1,4) OR =--LEFT(A1,FIND(" ",A1)-1) for variable length numbers The double negation turns text to numbers best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Cynthia" wrote in message ... I am using Excel 2003 I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? Thanks in advance! -- Cynthia |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
Rick,
This is really close, but when following your directions I get mixed results: Paste Special Original data Helper column (Correct) 41 8th Ave North,104 41 8th Ave,North,104 1226 W Wylie,2E 1226 W,Wylie,2E 1221 Tree Hollow Dr Apt, 13105 1221 Tree Hollow Dr,Apt,13105 (Incorrect) 2400 James, Place 2400,James,Place 6511 87th Ave,NW 6511 87th,Ave,NW Is there anything that can be changed in your code that would allow all the addresses to Paste Special correctly? Thanks, Cynthia -- "Rick Rothstein (MVP - VB)" wrote: Assuming by "freestanding numbers" you mean those numbers at the end of the address, here is one possible way to do what you asked. Put this formula in an unused (helper) column... =SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) and copy it down to the last row of data. Then select those cells in the helper column and press Ctrl+C (or click Edit/Copy) to Copy them. Then select the first cell of your original data and click Edit/PasteSpecial, select the Values from the options and click OK. Press Esc to unselect the original copy area and delete the helper column's data. You now have your original data with the last number delimited by a comma (although you may want to consider a different character in case there is a comma in the street address). Rick I am using Excel 2003 I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
Your initial post did not indicate that you have entries without the
trailing numbers in them. There is probably an easier way to do this, but here is what I came up with. Try this solution which uses two helper columns (assumed to be C and D for this example). Put this in C1... =MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255) and copy down. Next, put this in D1 and copy down... =IF(SUMPRODUCT(--ISNUMBER(FIND(MID(C1,ROW(INDIRECT("c1:A"&LEN(C1))) ,1),"0123456789")))0,SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),A1) Now do the Copy/Paste Special procedure to the contents of Column D (deleting both helper columns when through, of course). Rick "Cynthia" wrote in message ... Rick, This is really close, but when following your directions I get mixed results: Paste Special Original data Helper column (Correct) 41 8th Ave North,104 41 8th Ave,North,104 1226 W Wylie,2E 1226 W,Wylie,2E 1221 Tree Hollow Dr Apt, 13105 1221 Tree Hollow Dr,Apt,13105 (Incorrect) 2400 James, Place 2400,James,Place 6511 87th Ave,NW 6511 87th,Ave,NW Is there anything that can be changed in your code that would allow all the addresses to Paste Special correctly? Thanks, Cynthia -- "Rick Rothstein (MVP - VB)" wrote: Assuming by "freestanding numbers" you mean those numbers at the end of the address, here is one possible way to do what you asked. Put this formula in an unused (helper) column... =SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) and copy it down to the last row of data. Then select those cells in the helper column and press Ctrl+C (or click Edit/Copy) to Copy them. Then select the first cell of your original data and click Edit/PasteSpecial, select the Values from the options and click OK. Press Esc to unselect the original copy area and delete the helper column's data. You now have your original data with the last number delimited by a comma (although you may want to consider a different character in case there is a comma in the street address). Rick I am using Excel 2003 I have files that display like this: 1234 Main St 3 4456 Elm St 1154 2711 Maple St 27G I need to identify these freestanding numbers with a "comma space" in front of them so I can split this text into the next column. Is there a way to do this using CTRL-H? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))),255) =IF(SUMPRODUCT(--ISNUMBER(FIND(MID(C1,ROW(INDIRECT("c1:A"&LEN(C1))) ,1),"0123456789")))0,SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),A1) I figured I should point this out, just in case... on my newsreader, the above two formulas got word wrapped at a blank space character making it hard to see that there is a blank space character between the quote marks. Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying text to split into columns
Thank you, Rick!
-- Cynthia "Rick Rothstein (MVP - VB)" wrote: =MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255) =IF(SUMPRODUCT(--ISNUMBER(FIND(MID(C1,ROW(INDIRECT("c1:A"&LEN(C1))) ,1),"0123456789")))0,SUBSTITUTE(A1," ",",",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),A1) I figured I should point this out, just in case... on my newsreader, the above two formulas got word wrapped at a blank space character making it hard to see that there is a blank space character between the quote marks. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to columns, split at first space only | New Users to Excel | |||
macro to split text in columns | Excel Discussion (Misc queries) | |||
split text , insert to columns | Excel Discussion (Misc queries) | |||
Split text without using data-text to columns | Excel Discussion (Misc queries) | |||
How do I split 16 char text 'XX XXX= +. X' into 16 columns | Excel Discussion (Misc queries) |