another text to column problem

[gbeard]

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,

 

[Biff]

See previous post for a possible solution

Biff

 

[gbeard]

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.

 

[Biff]

Hi!

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

=LEFT(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)

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

Biff

 

[gbeard]

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

 

[Biff]

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]

Biff,
I'm amazed at what you guys can do with these functions. I'm not really
working on a spreadsheet...I'm just posting ideas that I figure can't be
done to see if anyone can do them. JUST KIDDING!!!
I didn't get this last one to work. I'm getting a #N/A for this one.
BTW, what does the +1,255 at the end do? And, where should I go to find out
what all these functions do? I know what most of them do in and of
themselves, but trying to figure out what they are doing when joined with
many others takes me a LONG time to figure out sometimes (like this one). I
spend a lot of time at:
http://www.techonthenet.com/excel/formulas/index_alpha.htm but it doesn't
explain how to use functions together.
Thanks for the time you're putting in for me...I really do appreciate it.

 

[gbeard]

Biff,
When do you enter a formula as an array as opposed to just hitting RETURN?
I just entered the last string as an array and get a #VALUE! error.

BTW, I do love this. I've been working with Excel for years and just
started using this NG a couple of weeks ago. I'm amazed at how much I've
learned in the last couple of weeks thanks to this NG.

 

[gbeard]

Biff,
Let me guess...does the +1255 allow for that many letters to "get"? Also,
what does the >47 & <58 do?

 

[Biff]

Can you send me a sample of your data?

It doesn't have to be the whole workbook. You said you have the address in a
few columns, maybe just copy some of that data to a single sheet and send me
that sheet. Everything I suggested I tested in MY test sheet but it's not
YOUR sheet!

If you can do that I'll have a better idea of what you need.

Here's where you can contact me:

xl is no help at comcast period net

Get rid of "is no" and change the obvious.

Biff

 

[Biff]

Hi!

In that formula FIND looks for a space and uses the MATCH function to tell
it where to START looking. MATCH returns the position of the first digit in
the string.

+1 means: FIND the space and add 1 to the position at which the space is
found since you want to extract data to the right of the space. Assume the
space is in position 10 of the string. The data you want to extract starts
in position 11.

255 is the number of characters to return. Basically, 255 is just an
arbitrary large number that pretty much guaratees that everything to the
right will be returned without having to actually count how many characters
there are from the starting point of FIND to the end of the string.

47 and <58:

Those are the character code numbers for the digits. The char code for zero
is 48 and the char code for 9 is 57.

0 = 48
1 = 49
2 = 50
3 = 51
...
...
9 = 57

In the formula, we want to find the first instance any char code that is
greater than char code 47 and less than char code 58.

Biff

 

[gbeard]

Ah Biff,
That helps a lot! I wish more answers would come with explanations. I used
to like my homework to be done for me and I didn't care why it was correct
or not. But now, I'd like to understand what these functions are doing so I
don't have to ask a dozen questions about the same function.
I'm getting close to figuring this one out. I've been able to break it down
and see what everything does. It's just a matter of putting everything
together the right way.