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

 

[Chekie]

If the data is simply like that then

=Trim(Substitute(Substitute(A1,"Bill #",""),"realized",""))

 

[Robert Mika]

if you need a dash beetween letter and numbers Try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Bill #",""),"realized",""),"0",""),"C","C-0000")," ","")

 

[khawarameer]

Thanks.

But the data which i provided was only a sample. the actual data is far more complicated. And the formulas given above don't work on them. The data is as under;

<table style="border-collapse: collapse; width: 271pt;" width="361" border="0" cellpadding="0" cellspacing="0"><col style="width: 271pt;" width="361"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 271pt;" width="361" height="17">ABC-5801 REALIZED LIABILITY REVERSED</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">ABC-3969 BILL REALISED

Debit Transfer</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BILL REALIZED ABC-9294, MALIK & CO.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LIABILITY REVERSED ABC-5609</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LIABILITY REVERSED ABC-5359</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">"ABC-4369 " LIABILITY REALIZED. NOW ENTRY REVERSED.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LIABILITY REVERSED ABC000005337 EBP-1607</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LIABILITY REVERSED ABC-38005 MALIK @ CO.</td> </tr> </tbody></table>
as you can see above my desired information is placed at different areas.

now can you create a formula to extract these values.

regards

 

[Chekie]

Paste the code onto a standard module

Private RegX As Object
 
Private Sub SetRegX()
Set RegX = CreateObject("VBScript.RegExp")
End Sub
 
Function GetTxt(ByVal txt As String) As String
If RegX Is Nothing Then SetRegX
With RegX
    .Pattern = "(\b|"")(\d{3}-\d+)(""?\b)"
    GetTxt = .replace(txt, "$2")
End With
End Function

Then Use in cell like

=GetTxt(A1)

 

[khawarameer]

Dear Chekie,

The code is copying entire row. Its not extracting the desired data.

 

[Chekie]

Can you change

.Pattern = "(\b|"")(\d{3}-\d+)(""?\b)"

to

.Pattern = "(\b|"")(\D{3}-\d+)(""|\b)"

 

[bosco_yip]

Try………

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

Regards
Bosco

 

[khawarameer]

Sorry Chekie it still does'nt work.

bosco's formula worked though.
Thanks bosco.

However the formula does'nt work on this data.

"LIABILITY REVERSED ABC000005337 EBP-1607"

you see the area in red does not contain "-" in it. so the formula retuns blank. Can you modify your forlmula a little bit so that values such as above may also be extracted.

regards

 

[bosco_yip]

However the formula does'nt work on this data.
"LIABILITY REVERSED ABC000005337 EBP-1607"

Hi, khawarameer ,

It is because your 2nd group of datas at #4, " ABC000005337 " didn't highlighted with red color

OKm please try…….

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

Regards
Bosco