PDA

View Full Version : Solved: Extracting just the Number in a name



MrAshton
03-20-2008, 09:26 AM
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
03-20-2008, 09:36 AM
The VAL function should do this.

MrAshton
03-20-2008, 09:57 AM
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
03-20-2008, 10:04 AM
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
03-20-2008, 10:05 AM
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
03-20-2008, 10:08 AM
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
03-20-2008, 10:09 AM
Success!
all it needed was the 'start as long' position.

Thanks guys!

Bob Phillips
03-20-2008, 10:13 AM
If not, this trivial formula does it



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))

mdmackillop
03-20-2008, 10:15 AM
It failed with me too, but it SHOULD work!!!
I should read the Help in more detail!:(