Solved: Extracting just the Number in a name

[MrAshton]

Hey guys. So I set up a code to mine data out of multiple workbooks. I pull the data from each workbook and put it into a new sheet within a single workbook. I then take the data and create a summary with all the data from all worksheets in a single table on one worksheet. I need to be able to use the number out of the worksheet titles as the header for each dataset in the table.

Worksheet titles are as follows:
HK284_SD_Depth_of_Focus
HK293_SD_Depth_of_Focus
HK226_SD_Depth_of_Focus ... etc

I hope this makes sense. the only part I need help with is getting the 3 digit number from the worksheet name into a stored value in VB.

Thanks!

[mdmackillop]

The VAL function should do this.

[MrAshton]

ok I've tried the following and none work

curSN = Val(activeworkbook.name)
Problem: returne 0 (probably because the first to characters in the name are letters

curSN = val(mid(activeworkbook.name))
Problem: does not accept Val and Mid together

I've also tried:
curSN = val(string(...etc

any ideas?

[mdmackillop]

It failed with me too, but it SHOULD work!!!

If no-one comes up with a better solution, it can be done by checking each ascii value in the string.

[RichardSchollar]

Hi

If there's always two letters at the front then you could use:

curSN = val(mid(activesheet.name,3))

I changed to Activesheet.Name as I wasn't sure if you were working with sheets or workbook names - you can always amend back.

Richard

[MrAshton]

either works because the sheet name is the same as the workbook name. the previous code generated the two to be identical.

I'll give this a shot. I was wondering about the ",3" portioin of the code because I used it elsewhere before but didnt know the precise function of it.

[MrAshton]

Success!
all it needed was the 'start as long' position.

Thanks guys!

[Bob Phillips]

If not, this trivial formula does it

[vba]

Const BASE_FORMULA As String = _
"MID(""<val>"",MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},""<val>0123456789""))," & _
"SUMPRODUCT(LEN(""<val>"")-LEN(SUBSTITUTE(""<val>"",{0,1,2,3,4,5,6,7,8,9},""""))))"

wsTitle = "HK284_SD_Depth_of_Focus"
MsgBox ActiveSheet.Evaluate(Replace$(BASE_FORMULA, "<val>", wsTitle))

[/vba]

[mdmackillop]

It failed with me too, but it SHOULD work!!!

I should read the Help in more detail!