Extracting Numeric Values from an Alpha/Numeric String

**Delkath** · 10-26-2010, 03:51 PM

I am struggling to find the most profecient way to clean up data and need some expert advice. My company has a two fields in one of our tables, Desc1 and Desc2. Each of these fields is alpha/numeric and has a character limitation of 24 characters, 48 when combined. I want to find a way to extract only certain parts of the string in which I will use to concatanate with other data elements. This pertanent data can reside anywhere inside of these two fields and is not format. IE, I want to extract only the measurement portion of the string (bolded) in excel and then remove that from the source. I guess once I have the desired data in a new cell a simple SUBSTITUTE will work, so just extracting it into another cell will work fine. If this would work for even 70% of the data it would be more then satisfactory. Any ideas?

.0027 X 1.34 X 8.75 Copper Foil
.001 X .490" Copper Foil 2592 Ft. Increments
.001 X .70" Copper Foil 1728 Ft Increments
.001 X .875" Copper Foil 216Ft Increments
.001" X 0.25" X 108' Cop Cuf W/ 2345-1, 11/32"
.001"X.310"X108' Copper Cuffed W/ .57" 3M #56
.001"X.70"X216' Copper Foil Cuffed With 3M#1350
Usf Copper Cent Intrtape .0014In X 0.75In X 180Ft
Usf Copper Foil .002In X 0.35In 110Annld
Usf Copper Foil; Inc180F .0014Inx0.750In 110 Ann.

**NBVC** · 10-26-2010, 04:33 PM

Try:

Please Login or Register  to view this content.

where A1 contains first string to parse.

Then you can copy and paste special Values over the column to remove the formulas.

This assumes that all dimensions start with a decimal point.... as per samples

if you want to break it down into 2 separate formulas so it is easier to follow...

Then in B1:

Please Login or Register  to view this content.

This finds the first decimal point and extracts everything to the right of it.. and then it replaces the <space>X<space> and x with just X.. so then we can look for first space and cut off there...

and in C1:

Please Login or Register  to view this content.

... looks for first space in the extract strings in column B and cuts the string there.. then replaces the X with the X surrounded by spaces...

**sailepaty** · 10-26-2010, 10:45 PM

One more,

=SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND(".",A1)-1,"")," X ","X"),FIND(" ",SUBSTITUTE(REPLACE(A1,1,FIND(".",A1),"")," X ","X")&" ")),"X", " X ")

Regards

**Delkath** · 10-27-2010, 10:36 AM

Awesome, this did exactly what was needed. Thank you both. Not every case starts with a decimal however. Is there a way to grab the numeric characters without searching on the decimal?

ie;
Tongue & Groove Plier Set; 7in 10in 12in
Lufkin Power Ret Tape Measure; 1/2in X 12ft
#1 X 6in Xcelite Blade

**NBVC** · 10-27-2010, 10:46 AM

Not based on your sampleset...

for example:

Usf Copper Foil; Inc180F .0014Inx0.750In 110 Ann.

how would you exclude the 180F part?

unless there is a specific pattern, it is difficult to do.. .that is why the decimal seemed like a good starting point. Perhaps the rest will need to be done manually...

**Delkath** · 10-27-2010, 02:36 PM

I guess I was thinking along the lines of; instead of searching for a decimal, there was a way you could search for a numeric string, followed by "in X ####in" where # is a numeric string. So if it there is a numeric value followed by "in" and then "X" and then another numeric value ending with "in". Since some of the numeric strings would contain a decimal in it, there would have to be an exception for that as well. lol.

I know that is probably asking the impossible, just wishful thinking. Your applied solution has already saved me a load of time and I thank you for that!

ie
Static Control 1000; 16.4in X 24in Top Bag. 100/EA = 16.4in X 24in (desired results.)