# 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

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 A1do a Text To Columns to split the string at the semi-coloninto three seperate cellsThis is for 2007:Highlight cell A1On the ribbon select the DATA tabSelect Text to Columnsselect the Delimited buttonClick NextFrom the list of Delimiters, check SemicolonClick NextClick FinishYou should now have three cells, A1, B1, C1with 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 seperatedby a comma.See how that works for you.MIKEhttp://www.skeptic.com/

#1
April 23, 2013 at 03:41:43
 Try this:With your string of text in cell A1and your target text in cell B1enter the formula in cell C1=IF(NOT(ISERR(SEARCH(B1,A1))),B1,"")See how that works.MIKEhttp://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:921002792100289210029in 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!!!

Report •

Related Solutions

#4
April 24, 2013 at 06:23:13

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: 90380289038036So 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