Text String - Specific Characters

[Guest]

I've got a list of data in column "A" that looks like this:

A1 Red 26-57-98P Top Shelf
A2 Blue 3-18-46Q Top Shelf
A3 Green 46-9-38Z Top Shelf

etc....

from the text string, I would like to isolate only the numbers and dashes in
new cells in column "B" to look like this:

B1 26-57-98P
B2 3-18-46Q
B3 46-9-38Z

Is there a function to do that easily?

Thx

 

[Biff]

Hi!

Try this:

Enter this formula in B1 and copy down as needed:

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1))

Biff

 

[Guest]

If it is consistently in the format color, space, number you want to isolate,
space, then this seems to work:

=MID(A1,FIND(" ", A1) + 1,FIND(" ", A1, FIND(" ", A1) + 1) - FIND(" ", A1) -
1)

However, when I tried it on your test data, I did have some problems. I
don't know if it was because of how i did the copy, or if your data actually
is separated by 2 spaces. If the latter, than that formula would need
tweaking (or might not work at all depending on consistency of data entry.)

 

[Biff]

However, when I tried it on your test data, I did have some problems.

Tip:

Whenever you copy/paste sample data from a post it's a good idea to use
Edit>Replace to get rid of all the char 0160 non-breaking spaces. I do it
almost every time.

Biff

 

[Guest]

I thought it wasn't a normal space (or at least the 1st one.) Thanks for the
tip.

 

[Ron Rosenfeld]

I've got a list of data in column "A" that looks like this:

A1 Red 26-57-98P Top Shelf
A2 Blue 3-18-46Q Top Shelf
A3 Green 46-9-38Z Top Shelf

etc....

from the text string, I would like to isolate only the numbers and dashes in
new cells in column "B" to look like this:

B1 26-57-98P
B2 3-18-46Q
B3 46-9-38Z

Is there a function to do that easily?

Thx

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

Then try this formula:

=REGEX.MID(A1,"\s\S+\s")

The "regular expression" returns out the second sequence that is bounded by
<space>'s, without returning the <space>'s. That should do what you want.


--ron

 

[Ron Rosenfeld]

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

Then try this formula:

=REGEX.MID(A1,"\s\S+\s")

The "regular expression" returns out the second sequence that is bounded by
<space>'s, without returning the <space>'s. That should do what you want.

Typo alert. (Actually I copied an initial version of the formula that will
return the spaces.)

Should be:

=REGEX.MID(A2,"(?<=\s)\S+(?=\s)")

--ron

--ron