Letter/Number Split
 

Hi everyone,
Im currently making a database by copying fields from another database,
and I have some problems with separating numbers and letters.
An example of what im trying to do is-
Trying to split

UNIT 7 5-9 WOON GARDENS
24 RYDER ROAD
6 JESTER COURT
11 GRADER ROAD
4 ALEN PLACE
7 WILENSON LANE
UNIT 1 FAR ROAD
SHOP 771 CANTON HWY

So there are 2 different cells. One containing the writing upto and
including the last number, and one with the remaining.
For example

Column A
UNIT 7 5-9
24
6
11
4
7
UNIT 1
SHOP 771

Column B
WOON GARDENS
RYDER ROAD
JESTER COURT
GRADER ROAD
ALEN PLACE
WILENSON LANE
FAR ROAD
CANTON HWY

Is this actually possible? I tried text to columns, but that didn't
really work too well. Any Ideas?
Thanks,
Andrew


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile: http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=379592

First cell:
=LEFT(A1,MAX(ISNUMBER((MID(A1,ROW($1:$100),1)*1))* ROW($1:$100))+1)
Second:
=MID(A1,MAX(ISNUMBER((MID(A1,ROW($1:$100),1)*1))*R OW($1:$100))+2,99)

both are array formulae, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"carlito_1985"
wrote in message
news:carlito_1985.1qpm6h_1118909106.9065@excelforu m-nospam.com...

Hi everyone,
Im currently making a database by copying fields from another database,
and I have some problems with separating numbers and letters.
An example of what im trying to do is-
Trying to split

UNIT 7 5-9 WOON GARDENS
24 RYDER ROAD
6 JESTER COURT
11 GRADER ROAD
4 ALEN PLACE
7 WILENSON LANE
UNIT 1 FAR ROAD
SHOP 771 CANTON HWY

So there are 2 different cells. One containing the writing upto and
including the last number, and one with the remaining.
For example

Column A
UNIT 7 5-9
24
6
11
4
7
UNIT 1
SHOP 771

Column B
WOON GARDENS
RYDER ROAD
JESTER COURT
GRADER ROAD
ALEN PLACE
WILENSON LANE
FAR ROAD
CANTON HWY

Is this actually possible? I tried text to columns, but that didn't
really work too well. Any Ideas?
Thanks,
Andrew


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile:
http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=379592

This will get you part of the way

=LEFT($A1,SEARCH(" ",$A1)-1) for everything before the first space
=MID($A1,SEARCH(" ",$A1)+1,255) for everything after the first space

If you want to do different things when the cell is UNIT or SHOP, you can do
that as well.


"carlito_1985" wrote:


Hi everyone,
Im currently making a database by copying fields from another database,
and I have some problems with separating numbers and letters.
An example of what im trying to do is-
Trying to split

UNIT 7 5-9 WOON GARDENS
24 RYDER ROAD
6 JESTER COURT
11 GRADER ROAD
4 ALEN PLACE
7 WILENSON LANE
UNIT 1 FAR ROAD
SHOP 771 CANTON HWY

So there are 2 different cells. One containing the writing upto and
including the last number, and one with the remaining.
For example

Column A
UNIT 7 5-9
24
6
11
4
7
UNIT 1
SHOP 771

Column B
WOON GARDENS
RYDER ROAD
JESTER COURT
GRADER ROAD
ALEN PLACE
WILENSON LANE
FAR ROAD
CANTON HWY

Is this actually possible? I tried text to columns, but that didn't
really work too well. Any Ideas?
Thanks,
Andrew


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile: http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=379592

Hello,

If you want a generic solution for such string manipulations, you could try the
REGEX.MID function (available at http://xcell05.free.fr/english, Morefunc.xll
addin). It uses regular expressions to split the text.

Column A : =REGEX.MID(A1,".*\d+")
Column B : =REGEX.MID(A1,"(?<!\d)[^\d]+$")

Cordially,

Laurent

carlito_1985 a écrit :
Hi everyone,
Im currently making a database by copying fields from another database,
and I have some problems with separating numbers and letters.
An example of what im trying to do is-
Trying to split

UNIT 7 5-9 WOON GARDENS
24 RYDER ROAD
6 JESTER COURT
11 GRADER ROAD
4 ALEN PLACE
7 WILENSON LANE
UNIT 1 FAR ROAD
SHOP 771 CANTON HWY

So there are 2 different cells. One containing the writing upto and
including the last number, and one with the remaining.
For example

Column A
UNIT 7 5-9
24
6
11
4
7
UNIT 1
SHOP 771

Column B
WOON GARDENS
RYDER ROAD
JESTER COURT
GRADER ROAD
ALEN PLACE
WILENSON LANE
FAR ROAD
CANTON HWY

Is this actually possible? I tried text to columns, but that didn't
really work too well. Any Ideas?
Thanks,
Andrew