Pulling Text From String

[Yeezus]

Hi all,

I'm looking to pull a certain part of a string. For example let's say I have in Cell A1:

Item 1 1/2in - 3/4in, A/AA - Price

But out of that string I only want:

1/2in - 3/4in

What would b the best way to approach this?

Thanks much,
Y

 

[Gilliam]

If all the text strings look like that then

=MID(A1,8,13)

would get what you need.

 

[Yeezus]

Thanks for the quick reply Gilliam. Unfortunately the text strings may vary so I was looking for a code that would apply to all formats.

For example it could be:

Item 1 1/2in - 3/4in, A/AA - Price
Multiple Item 1 1/2in - 3/4in, A/AA - Price Max
Equipment 2 1/2in - 3/4in, A/AA - Price Min Per

Edit*
To add on to that the Units of Measure can be different as well:
Item 1 1in - 3/4in, A/AA - Price
Item 1 1/2in - 3ft, A/AA - Price
etc.


I've been playing around with the find function but can't figure out how to differentiate the multiple "-" and "/".

Thanks,
Y

 

[NeonRedSharpie]

How do you determine what you want out of the string?

 

[Jonmo1]

Is it always a single digit # to the left of the first / ?
And is tehre always a comma after the 2nd fraction ?

 

[Rick Rothstein]

For example it could be:

Item 1 1/2in - 3/4in, A/AA - Price

In addition to the other questions you have been asked... for the above example, what if the first number (the 1/2in) was a number greater than one with a fraction (say two-and-one-half)... what would the complete line of text look like (I am interested in how the two would be connected to the one-half and how that fits in with the number following the Item)?

 

[Yeezus]

How do you determine what you want out of the string?

Out of the string I would only like the Unit of Measure

Is it always a single digit # to the left of the first / ?
And is tehre always a comma after the 2nd fraction ?

1. For the most part, yes there is only a single digit to the left of the first "/"
2. Generally that is the case, but there are a few instances where that is not the case.

In addition to the other questions you have been asked... for the above example, what if the first number (the 1/2in) was a number greater than one with a fraction (say two-and-one-half)... what would the complete line of text look like (I am interested in how the two would be connected to the one-half and how that fits in with the number following the Item)?

The "1" before the "1/2" just signifies what number item it is. But sometimes it can be like you stated. For example:

Item 1 1 1/2in - 3/4in, A/AA - Price

In the case above the first 1 is the item number and the Unit of Measure that I want would be:
1 1/2in - 3/4in

The same could be said about:
Item 1 1/2in - 4 3/4in, A/AA - Price

Unit of Measure I'd want:
1/2in - 4 3/4in

Thanks a lot for the help,
Y

*Edit*

What I'm thinking is splitting parts of the Unit of Measure in multiple columns and then concatenating them.

 

[Jonmo1]

1. For the most part, yes there is only a single digit to the left of the first "/"
2. Generally that is the case, but there are a few instances where that is not the case.

So the answers to those questions are NO then, since the question was looking for an absolute "Always"

All these questions we're asking are trying to find a consistent pattern that can be used to extract the data.
We need a pattern that is applicable in ALL cases.

Is it fair to say you want the fraction AFTER the item #?
Is there ALWAYS an item # present?
Is there ALWAYS a space after that item #?


Now for the comma after the 2nd fraction.
Instead of a comma, is it ALWAYS followed by A/AA ?

 

[Yeezus]

So the answers to those questions are NO then, since the question was looking for an absolute "Always"

All these questions we're asking are trying to find a consistent pattern that can be used to extract the data.
We need a pattern that is applicable in ALL cases.

Is it fair to say you want the fraction AFTER the item #?
Is there ALWAYS an item # present?
Is there ALWAYS a space after that item #?


Now for the comma after the 2nd fraction.
Instead of a comma, is it ALWAYS followed by A/AA ?

That's the frustrating part, there are patterns for certain groups of lines and then that same pattern won't apply for another group. I'll show just some of the Line Items:

[TABLE="width: 315"]
<tbody>[TR]
[TD="width: 420, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Item   17 1/2in -28in, E/EE - PRICE MINI EXT PER SET[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="width: 420, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Items 17 1/2in -28in, E/EE - PRICE PER SET[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="width: 420, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Items 3/4in -6 3/4in, A/AA - PRICE MINI EXT PER SET[/COLOR][/SIZE][/FONT][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 315"]
<tbody>[TR]
[TD="width: 420, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]6in - 7   7/8in PDC, Minimum[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
</tbody>[/TABLE]

1. Yes, the fraction after the Item is what I'd like.
2. No, item number is not always present.
3. Yes, for this particular problem it is followed by A/AA

Thanks,
Y

 

[Rick Rothstein]

2. No, item number is not always present.

What exactly do you mean when you say that? You examples are not completely clear in this respect. If the item number is not there, could the item name still be there? For example, let's say you text was this...

Widgets 2 3/4in - 7/8in, A/AA - PRICE PER SET

could it ever be like this (where only the item number itself is not there)...

Widgets 3/4in - 7/8in, A/AA - PRICE PER SET

If so, how would we ever know if the 2 in the following belongs to the item name or the measurement?

Widgets 2 3/4in - 3 1/4in, A/AA - PRICE PER SET