Text String - Specific Characters

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

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

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

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.)

--
Kevin Vaughn


"Kiser" wrote:

> 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

>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

"Kevin Vaughn" <[email protected]> wrote in message
news:[email protected]...
> 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.)
>
> --
> Kevin Vaughn
>
>
> "Kiser" wrote:
>
>> 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

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


"Biff" wrote:

> >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
>
> "Kevin Vaughn" <[email protected]> wrote in message
> news:[email protected]...
> > 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.)
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Kiser" wrote:
> >
> >> 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
>
>
>

On Thu, 9 Feb 2006 14:07:27 -0800, "Kiser" <[email protected]>
wrote:

> 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

On Thu, 09 Feb 2006 20:35:38 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Thu, 9 Feb 2006 14:07:27 -0800, "Kiser" <[email protected]>
>wrote:
>
>> 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.
>
>

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