Solved Find multiple values in Excel string

April 23, 2013 at 03:04:49
Specs: Windows 7
Hello
I have long string that looks like that:
"54665 - Name 6420, 5412, 5224, 4454, 4408 - SSL Acceleration - Upgrade from 500 CPS to 2,000 SSL CPS; 9210027 - Upgrade from 500 CPS to 2,000 "
I wont to search for exist value 9210027 inside the string and copy it to anither column.
Any function to do it ?

Thank you!


See More: Find multiple values in Excel string

Report •


✔ Best Answer
April 24, 2013 at 08:33:07
Assuming that the parts numbers that are embedded in the string are always preceded by a "semicolon space"
then try this:

With your data in cell A1
do a Text To Columns to split the string at the semi-colon
into three seperate cells

This is for 2007:

Highlight cell A1
On the ribbon select the DATA tab
Select Text to Columns
select the Delimited button
Click Next
From the list of Delimiters, check Semicolon
Click Next
Click Finish

You should now have three cells, A1, B1, C1
with your seperated data and
each cell should begin with your part number.


Now in cell D1 enter the formula:

=LEFT(A1,FIND(" ",TRIM(A1),1))&", "&LEFT(B1,FIND(" ",TRIM(B1),1))&", "&LEFT(C1,FIND(" ",TRIM(C1),1))

In essence it is the same formula repeated three times and
concatenates the three cells A1, B1, C1 together seperated
by a comma.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
April 23, 2013 at 03:41:43
Try this:

With your string of text in cell A1
and your target text in cell B1
enter the formula in cell C1

=IF(NOT(ISERR(SEARCH(B1,A1))),B1,"")

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
April 23, 2013 at 03:49:04
Your subject line mentions "multiple values" but your question only mentions searching for one.

Is there something you haven't told us?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
April 24, 2013 at 01:16:12
Yes i am sorry.
I wont to get multiple values from the string.
I have list of part numbers
like:
9210027
9210028
9210029

in my string i have part numbers + description to the part.
"921002 - description ; 9210027 - description ; 9210029 - description"
I need only the part numbers from my list above without description.
like that
9210027,9210029

BIG BIG thanks for your reply!!!


Report •

Related Solutions

#4
April 24, 2013 at 06:23:13
In order for us to help you in the best manner, you need to be very specific when describing your data. Keep in mind that we cannot see your spreadsheet from where we are sitting so we don't really know what you date looks like.

In your OP your example was:

"54665 - Name 6420, 5412, 5224, 4454, 4408 - SSL Acceleration - Upgrade from 500 CPS to 2,000 SSL CPS; 9210027 - Upgrade from 500 CPS to 2,000 "

In your latest response, your example is:

"921002 - description ; 9210027 - description ; 9210029 - description"

Based on those 2 examples, can I assume that the first set of numbers in the string is a part number. e.g.

From Example 1: 54665

From Example 2: 921002

Can I further assume that the parts numbers that are embedded in the string are always preceded by "semi-colon space"?

Finally, is there a set number of part numbers in each string or do some have 2 part numbers, some have 3, some have just 1, etc.?

Perhaps a couple of examples of exact and complete strings would be helpful.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 24, 2013 at 07:28:39
Hi
This is how my string looks like:
"9038024 - AppDirector xx08, xx04, xx16 - Compression - Upgrade from 100 Mbps to 250 Mbps - Software Option ; 9038028 - AppDirector xx08, xx08 XL, xx04 XL, xx16 XL on ODS 2 XL, xx16 on ODS 3 v.2 - Compression - Upgrade from 500 Mbps to 750 Mbps ; 9038036 - AppDirector xx08, xx04, xx16 - Compression - Upgrade from 250 Mbps to 500 Mbps - Software Option "

Only 9038024, 9038028, 9038036 part numbers here.
I want to filter the following string from descriptions and some part numbers.
I have some row with list of valid part numbers.
For example:
9038028
9038036
So all the string above I want to replace with string that looks like that :
"9038028 , 9038036"


Report •

#6
April 24, 2013 at 08:33:07
✔ Best Answer
Assuming that the parts numbers that are embedded in the string are always preceded by a "semicolon space"
then try this:

With your data in cell A1
do a Text To Columns to split the string at the semi-colon
into three seperate cells

This is for 2007:

Highlight cell A1
On the ribbon select the DATA tab
Select Text to Columns
select the Delimited button
Click Next
From the list of Delimiters, check Semicolon
Click Next
Click Finish

You should now have three cells, A1, B1, C1
with your seperated data and
each cell should begin with your part number.


Now in cell D1 enter the formula:

=LEFT(A1,FIND(" ",TRIM(A1),1))&", "&LEFT(B1,FIND(" ",TRIM(B1),1))&", "&LEFT(C1,FIND(" ",TRIM(C1),1))

In essence it is the same formula repeated three times and
concatenates the three cells A1, B1, C1 together seperated
by a comma.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •


Ask Question