Identifying text to split into columns

[Cynthia]

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!

 

[Bernard Liengme]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Cynthia]

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
--

 

[Cynthia]

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\)]

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

 

[Rick Rothstein \(MVP - VB\)]

=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

 

[Cynthia]

Thank you, Rick!