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!

✔ 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 aText To Columnsto split the string at the semi-colon

into three seperate cellsThis is for 2007:

Highlight cell A1

On the ribbon select the DATA tab

SelectText to Columns

select theDelimitedbutton

Click Next

From the list of Delimiters, checkSemicolon

Click Next

Click FinishYou 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

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

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.

Yes i am sorry.

I wont to get multiple values from the string.

I have list of part numbers

like:

9210027

9210028

9210029in 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,9210029BIG BIG thanks for your reply!!!

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.

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"

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 aText To Columnsto split the string at the semi-colon

into three seperate cellsThis is for 2007:

Highlight cell A1

On the ribbon select the DATA tab

SelectText to Columns

select theDelimitedbutton

Click Next

From the list of Delimiters, checkSemicolon

Click Next

Click FinishYou 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

Ask Your Question

Weekly Poll