Specific data format search (VBA)

[Reevesy1988]

Hi guys,

I am a beginner when it comes to VBA code. I can write the simplest of codes however am having trouble with one.

I have a table of data (a GL download) and am creating a reconciliation macro.

As part of this macro i need it to look for a specific format of data, within a certain column for each row and enter this data in the next empty column.

The data in the cell in question may be 2 different formats;

adX0000X - blah blah blah
AD X0000X - blah blah blah
X0000X - blah blah blah

This will always be the beginning of the text however what i need it to extract is X0000X. Having said this X resembles any letter and 0 resembles any number. Also sometimes there may only be 3 numbers and other times 4


Any advice or guidance would be greatly appreciated.

Im currently reading through Bill Jelens book (VBA and Macros) which is very helpful but couldn't find anything for custom searches like this.

Thanks guys

 

[Snakehips]

Reevesy1988,

Welcome to Mr Excel.

Can you clarify....

Is the X0000X string always separated from bla bla bla by ' - ' ?

What is the 'certain column' ?

'2 different formats' ...... is actually 3 ?

Does 'next empty column' vary row by row or is it common to all rows ?

 

[Reevesy1988]

Hi Snakehips.

Thank you for your quick response, unfortunately it will not always be preceeded by " - " however it will always be the preceeded by a space and will only be the part of the text that contains both numbers and letters between spaces.

and apologies i did mean 3 rather than 2.

and by the next empty column i mean to all rows.

The column in question would be column C

Thank you again

 

[mikerickson]

You might use this formula
=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))-1,5+ISNUMBER(0+MID(A1, MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890"))+3,1)))

 

[Reevesy1988]

HI Mike

Thats perfect for finding the value, thank you.

Do you know what this would be as VBA / UCF?

Thanks

 

[Snakehips]

Check out Mikes code when there are no numbers in your string or just 1 or 2 numbers in your string as they will give you a return.
If there is no likelihood that your data will be anything other than the three formats you have illustrated then you could use Mike's formula directly in vba.
Assuming that your data has headers and so strings of interest start row 2 then...

Sub Extract()


lr = Cells(Rows.Count, "C").End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column + 1


With Range(Cells(2, lc), Cells(lr, lc))
.Formula = _
"=MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&" & "" & 1234567890 & "" & "))-1,5+ISNUMBER(0+MID(A2, (FIND({1,2,3,4,5,6,7,8,9,0},A2&" & "" & 1234567890 & "" & "))+3,1)))"
.Value = .Value
End With


End Sub

If there is an issue with the format then it will need tweaking.

 

[Reevesy1988]

Hey guys,

Thats perfect, i gave it a few tweaks and works wonders. Thank you