Extracting Desired data from raw data set.

[khawarameer]

Hello All,

I need to extract some part data from available raw data. The data format is like this ABC-00000000. The problem i am facing that this data is placed in rows that contain other information like this.

e.g. "Bill # ABC-00002345 realized"

some times the data is fed in this fashion

"Bill # ABC 2342 realized"
or
"Bill # ABC - 3232 realized"
or
"Bill # ABC000032324 realized"

now i need to extract the data highlighted in red and arrange it like data highlighted in green.

I tried to use mid and find function but could not arrange the extracted data in to desired form.

A VBA code or formula is required.

Thanks

 

[khawarameer]

Thanks bosco,

Formula worked greate.

 

[mikerickson]

Assuming that those strings are in column A:
This uses Named functions. Select A1 and define these names

Name: cleanString
RefersTo: =" "&SUBSTITUTE(Sheet1!$A1, ",", " ")

Name: dashPlace
RefersTo: =FIND("-", cleanString&"-")

Name: spaceBeforeDash
RefersTo: =MATCH(dashPlace, FIND(" ",cleanString,ROW(Sheet1!$1:$100)))

Name: spaceAfterDash
RefersTo: =FIND(" ", cleanString&" ", dashPlace)

Then, when entered in a cell in row 1, the formula
=TRIM(MID(cleanString,spaceBeforeDash,dashPlace-spaceBeforeDash))
will return the test data

=-MID(cleanString,dashPlace,spaceAfterDash-dashPlace)
will return the numeric data

 

[khawarameer]

Thanks Mike,

But your formula did not work on below mentioned string,

<TABLE style="WIDTH: 561pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=748><COLGROUP><COL style="WIDTH: 561pt; mso-width-source: userset; mso-width-alt: 31914" width=748><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 561pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=17 width=748>"LIABILITY REVERSED ABC00008239 REALIZED"</TD></TR></TBODY></TABLE>

you see in this case there in no "-". The formula returned the last word of the string.

Further the numeric value is also not displayed instead " #VALUE! " is displayed.

 

[khawarameer]

Hi bocso,

Just need a little more help.

=IF(ISNUMBER(FIND("ABC",A1)),"ABC"&SUBSTITUTE(LEFT(REPLACE(A1,1,FIND("ABC",A1)+2,""),FIND(" ",REPLACE(A1,1,FIND("ABC",A1)+2,"")&" ",4)),",",""),"")

Your second formula (above) worked fine. However in some data strings e.g.
"LIABILITY REVERZED ABC000008005 MALIK'S APPAREL"

It extracts all text mentioned in red. is there a way to put a "-" afte ABC and remove all zeeros that come between ABC & and succeeding number.

Regards

 

[facethegod]

try...


="ABC- "&TEXT(LOOKUP(1E+307,MID(SUBSTITUTE(A1,"-",""),FIND("ABC",A1)+3,ROW(A1:INDEX(A:A,LEN(A1))))+0),REPT(0,8))

Hope this helps

 

[bosco_yip]

........is there a way to put a "-" after ABC and remove all zeros that come between ABC & and succeeding number.

Please try…….

=IF(ISNUMBER(FIND("ABC",A1)),"ABC"&LOOKUP(2,-LEFT(TRIM(SUBSTITUTE(REPLACE(A1,1,FIND("ABC",A1)+2,""),"-","")),ROW(INDIRECT("1:255")))),"")

Regards
Bosco

 

[bosco_yip]

Or........

=IF(ISNUMBER(FIND("ABC",A1)),"ABC"&LOOKUP(1,-MID(SUBSTITUTE(A1,"-",""),FIND("ABC",A1)+3,ROW(INDIRECT("1:255")))),"")

Regards
Bosco

 

[khawarameer]

Thank you both for these formulas.

 

[khawarameer]

try...


="ABC- "&TEXT(LOOKUP(1E+307,MID(SUBSTITUTE(A1,"-",""),FIND("ABC",A1)+3,ROW(A1:INDEX(A:A,LEN(A1))))+0),REPT(0,8))

Hope this helps

Hi facethegod,

Can you please explain the highlighted (in red) argument.

Regards

 

[facethegod]

Hi facethegod,

Can you please explain the highlighted (in red) argument.

Regards

Hi khawarameer,

In the context of this formula i'm trying to extract the largerst number in the array arguement of the LOOKUP function-which in this case is an array of numbers (or error values) arranged in ascending order. If LOOKUP can't find the lookup_value (1E+307), it uses the largest value in the array that is less than or equal to lookup_value. I'm using 1E+307, b/c i expect no number in the array to be greater than this value. Therefore the function should always return the largest numnerical value in the array.

Hope this helps!