I have a sheet with data in 4 columns. Column A is the clients name,
address and city. Column B is the state the client is in. Column C is the
zip code of the client. And Column D is the clients phone number.
I need to break up Column A into 3 columns so that the clients name is in
Column A, their street address is in Column B and their city name is in
Column C. The length of the clients names, address' and city are all
different lengths. There are no punctuation marks. Some city names have 1
word, some have 2 and some even 3.
Is there a way to break this column into 3 columns?

Thanks yet again,
--
Gary Beard

See previous post for a possible solution

Biff

"gbeard" wrote in message
...
I have a sheet with data in 4 columns. Column A is the clients name,
address and city. Column B is the state the client is in. Column C is the
zip code of the client. And Column D is the clients phone number.
I need to break up Column A into 3 columns so that the clients name is in
Column A, their street address is in Column B and their city name is in
Column C. The length of the clients names, address' and city are all
different lengths. There are no punctuation marks. Some city names have
1 word, some have 2 and some even 3.
Is there a way to break this column into 3 columns?

Thanks yet again,
--
Gary Beard

Biff,
Can I use the FIND command to locate a number (not a specific number, but
rather, the first number) within a cell? This would solve part of my
problem, if I can extract everything before the first number I would end up
with the clients name.

--
Gary Beard

Hi!

Try this array formula. Entered with the key combo of CTRL,SHIFT,ENTER:

=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1))47)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1))<58),0)-1)

Will return everything to the left of the FIRST NUMBER (digit) found in the
string.

Biff

"gbeard" wrote in message
m...
Biff,
Can I use the FIND command to locate a number (not a specific number, but
rather, the first number) within a cell? This would solve part of my
problem, if I can extract everything before the first number I would end
up with the clients name.

--
Gary Beard

Biff,
Can I get everything right of the numbers by substituting RIGHT for LEFT?
Or is there a different way to do that?

--
Gary Beard

Hi!

Don't'cha love this! <g

I'm assuming that there's a space after the numbers? If not this won't work!

Try this array formula:

A1 = John Smith 1234 South St.

=MID(A1,FIND("
",A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1 ))),1))47)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),1))<58),0))+1,255)

Returns: South St.

Biff

"gbeard" wrote in message
. com...
Biff,
Can I get everything right of the numbers by substituting RIGHT for LEFT?
Or is there a different way to do that?

--
Gary Beard