Letter/Number Split

  • Thread starter Thread starter carlito_1985
  • Start date Start date
C

carlito_1985

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
 
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))*ROW($1:$100))+2,99)

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

--
HTH

Bob Phillips

"carlito_1985" <[email protected]>
wrote in message
news:[email protected]...
 
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.
 
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 :
 
Back
Top