Extracting part of Text from one cell to another

[JayW]

Simple I thought, but can anyone help

I have a column of cells with similar to the following text.

First Marine Avenue 18 1303 11 1

There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)

The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)

I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.

 

[Toppers]

Try this ( works on my testing assuming single blanks as delimiters)

Assuming data in A2

in B1:
=LEFT(A2,SEARCH({1,2,3,4,5,6,7,8,9,0},A2& "1,2,3,4,5,6,7,8,9,0")-1)

Enter the following with Ctrl+Shift+Enter (array formulae)

in C1:

=VALUE(MID($A2,SUM(LEN($B2:$B2))+1,FIND("
",$A2,SUM(LEN($B2:$B2))+1)-SUM(LEN($B2:$B2))-1))

in D1:
=VALUE(MID($A2,SUM(LEN($B2:$C2))+2,FIND("
",$A2,SUM(LEN($B2:$C2))+2)-SUM(LEN($B2:$C2))-2))

in E1:
=VALUE(MID($A2,SUM(LEN($B2:$D2))+3,FIND("
",$A2,SUM(LEN($B2:$D2))+3)-SUM(LEN($B2:$D2))-3))

in F1:
=VALUE(RIGHT($A2,LEN($A2)-(SUM(LEN($B2:$E2))+3)))

HTH

 

[Toppers]

.... cells should be B2, C2 etc not B1 ...

Try this ( works on my testing assuming single blanks as delimiters)

Assuming data in A2

in B1:
=LEFT(A2,SEARCH({1,2,3,4,5,6,7,8,9,0},A2& "1,2,3,4,5,6,7,8,9,0")-1)

Enter the following with Ctrl+Shift+Enter (array formulae)

in C1:

=VALUE(MID($A2,SUM(LEN($B2:$B2))+1,FIND("
",$A2,SUM(LEN($B2:$B2))+1)-SUM(LEN($B2:$B2))-1))

in D1:
=VALUE(MID($A2,SUM(LEN($B2:$C2))+2,FIND("
",$A2,SUM(LEN($B2:$C2))+2)-SUM(LEN($B2:$C2))-2))

in E1:
=VALUE(MID($A2,SUM(LEN($B2:$D2))+3,FIND("
",$A2,SUM(LEN($B2:$D2))+3)-SUM(LEN($B2:$D2))-3))

in F1:
=VALUE(RIGHT($A2,LEN($A2)-(SUM(LEN($B2:$E2))+3)))

HTH

 

[Ron Rosenfeld]

Simple I thought, but can anyone help

I have a column of cells with similar to the following text.

First Marine Avenue 18 1303 11 1

There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)

The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)

I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.

One way is to use Regular Expressions.

You can download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then you can use these formulas with your full text string in A1:

B1: =REGEX.MID(A1,".*(?=\s+(\d+\s+){3}\d+)")
C1: =REGEX.MID($A$1,"\d+",COLUMNS($A:A)-5)

Copy/drag across to F1

It can be done with worksheet formulas, but the formula is much more complex,
and morefunc.xll can be easily embedded in your workbook for distribution.

B1:
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))-3)))

C1:
=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-
LEN(SUBSTITUTE(TRIM(A1)," ",""))-3))+1,FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-2))-
FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))-3)))

D1:
=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-
LEN(SUBSTITUTE(TRIM(A1)," ",""))-2))+1,FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))-FIND(
CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))-2)))

E1:
=MID(TRIM(A1),1+FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-
LEN(SUBSTITUTE(TRIM(A1)," ",""))-1)),FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-FIND(
CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))-1)))

F1:
=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),
LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))),255)


--ron

 

[Ron Rosenfeld]

Simple I thought, but can anyone help

I have a column of cells with similar to the following text.

First Marine Avenue 18 1303 11 1

There will always be a final 4 groups of text numbers, each with a space
between, but each having different number of numbers (this particular
sequence has 2,4,2,1 - but others may be different, but always 4 groups)

The true text ( a series of Road names are potentiaaly all different, having
a sequence of words that may be up to 5 words long before the numbers start)

I need to extract the 4 groups of numbers into seperate cells, leaving the
Road name text in a singe cell. I would then have a table of 5 columns, the
first the Road text, and the next 4 columns being the group of numbers, which
I would then format as numbers.

I neglected to note that to convert the text strings, which my previous
formulas will produce, to numbers, precede each formula with a double unary.

In other words:

=--REGEX.MID(A1,...

or

=--MID(A1, ...

depending on if you use the morefunc.xll formulas, or the built-in worksheet
functions.
--ron

 

[Ron Rosenfeld]

Try this ( works on my testing assuming single blanks as delimiters)

Assuming data in A2

in B1:
=LEFT(A2,SEARCH({1,2,3,4,5,6,7,8,9,0},A2& "1,2,3,4,5,6,7,8,9,0")-1)

Enter the following with Ctrl+Shift+Enter (array formulae)

in C1:

=VALUE(MID($A2,SUM(LEN($B2:$B2))+1,FIND("
",$A2,SUM(LEN($B2:$B2))+1)-SUM(LEN($B2:$B2))-1))

in D1:
=VALUE(MID($A2,SUM(LEN($B2:$C2))+2,FIND("
",$A2,SUM(LEN($B2:$C2))+2)-SUM(LEN($B2:$C2))-2))

in E1:
=VALUE(MID($A2,SUM(LEN($B2:$D2))+3,FIND("
",$A2,SUM(LEN($B2:$D2))+3)-SUM(LEN($B2:$D2))-3))

in F1:
=VALUE(RIGHT($A2,LEN($A2)-(SUM(LEN($B2:$E2))+3)))

HTH

Try these addresses:

123 First Marine Avenue 18 1303 11 1
First Marine Avenue 28 1303 11 1

Neither one seems to give the expected results using your formulas.


--ron

 

[Toppers]

Ron,

Thanks ... in B2 put:

=LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A3& "1,2,3,4,5,6,7,8,9,0")-1))

and enter as array formula.

re "123 First Marine Avenue 18 1303 11 1"

OP said street names ["The true text ( a series of Road names are
potentially all different, having a sequence of words that may be up to 5
words long before the numbers start)"] with no numbers ... or that was my
interpretation!

If "123 First " etc is valid, then my solution won't work.

Appreciate the feedback.

 

[JayW]

Thanks to Toppers and Ron. I have not yet had the opportunity to try your
solutions, but will do so within the next few hours. (And will report back
here ASAP)

Toppers you were correct - my list is a list of Road Names, not preceeded by
numbers.

I appreciate the help - I am sure I can now do the job I need. Many thanks

--
JayW, Hants, UK

Ron,

Thanks ... in B2 put:

=LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A3& "1,2,3,4,5,6,7,8,9,0")-1))

and enter as array formula.

re "123 First Marine Avenue 18 1303 11 1"

OP said street names ["The true text ( a series of Road names are
potentially all different, having a sequence of words that may be up to 5
words long before the numbers start)"] with no numbers ... or that was my
interpretation!

If "123 First " etc is valid, then my solution won't work.

Appreciate the feedback.

Try these addresses:

123 First Marine Avenue 18 1303 11 1
First Marine Avenue 28 1303 11 1

Neither one seems to give the expected results using your formulas.


--ron

 

[Ron Rosenfeld]

Ron,

Thanks ... in B2 put:

=LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A3& "1,2,3,4,5,6,7,8,9,0")-1))

and enter as array formula.

re "123 First Marine Avenue 18 1303 11 1"

OP said street names ["The true text ( a series of Road names are
potentially all different, having a sequence of words that may be up to 5
words long before the numbers start)"] with no numbers ... or that was my
interpretation!

If "123 First " etc is valid, then my solution won't work.

Appreciate the feedback.

Yes, your modification seems to work OK.


--ron

 

[JayW]

Ron,

I downloaded the morefunc.xll as you suggested. Works perfectly and very
simple to use.

As a relatively new user of Computers, I must thank you and Toppers for
taking the trouble to respond.

This may be another question (if so I will create a new thread), but is
there a way of creating a new Worksheet using the values in the new
cells/columns containing the absolute text & figures without the functions

 

[Ron Rosenfeld]

Ron,

I downloaded the morefunc.xll as you suggested. Works perfectly and very
simple to use.

As a relatively new user of Computers, I must thank you and Toppers for
taking the trouble to respond.

This may be another question (if so I will create a new thread), but is
there a way of creating a new Worksheet using the values in the new
cells/columns containing the absolute text & figures without the functions

Well, you can

1. Insert/Worksheet
2. On the old worksheet (with the formulas) select the range, then Edit/Copy
3. Navigate to the new worksheet
Select the upper left corner of the range
Edit/Paste Special/Values

The above can be automated via a macro, if you need to do this repeatedly.
--ron

 

[JayW]

Thank you

--
JayW, New Milton, Hants, UK

Well, you can

1. Insert/Worksheet
2. On the old worksheet (with the formulas) select the range, then Edit/Copy
3. Navigate to the new worksheet
Select the upper left corner of the range
Edit/Paste Special/Values

The above can be automated via a macro, if you need to do this repeatedly.
--ron

 

[Ron Rosenfeld]

Ron,

I downloaded the morefunc.xll as you suggested. Works perfectly and very
simple to use.

As a relatively new user of Computers, I must thank you and Toppers for
taking the trouble to respond.

You're welcome, Jay. Thanks for the feedback.

There are many other useful functions in morefunc.xll too.

Best wishes,
--ron