Extract string w/ 7 characters

[SteveDB1]

How all.
I have a worksheet that I want to extract 7 characters from.
Below is a sample of the first few rows that I'd need to look at for the
specific locations.

------------------------------------------------------------------------------
"Truckee River Claim No. 303 (404) SUMMARY"

"Decreed Owner: OWNER's NAME successor to Prior Land Holder's Name"

Page No.: 35 & 44

Orr Ditch (303) Sullivan and Kelly Ditch (404) Acre Ft. cfs Acres

Map: TR-087
------------------------------------------------------------------------------------------

While this is just text, we generally work off of the first 6 columns
(pretty much std), the first two rows are merged, and this actually varies
from workbook to workbook.
What I want to extract would be the numbers- in this case, 303 and (404)
Sometimes this will be more-- about 10 occurrences of 10 + characters-- most
of the time it'll only be 3 characters.
My goal is to extract these numbers, and rename a worksheet (insert those
into the worksheet name) from
SUM (some variation of sum, summary, etc...) to Sum (some basic variation
thereof)-###, where the ### digits of interest.

I expect more questions, so go for it.
Thank you for your helps.
Best.

 

[SixSigmaGuy]

The design of the code to extract this information will depend greatly on
the pattern of text that surrounds it the text you want. The more standard
that pattern is, the easier it will be to extract the number.

For example, will it always say exactly "Truckee River Claim No." before the
numbers you want to extract; and will it always say "SUMMARY" following the
numbers you want? If so, the following code will extract your numbers:

Function stGetNumber(stFullText as String) as String
Dim stNumber as String

stNumber = Trim(Mid(stFullText, Len("Truckee River Claim No."))
stNumber = Trim(Left(stNumber, Len(stNumber) - Len("SUMMARY"))

stGetNumber = stNumber
End Function

You lost me, though, because your subject line says 7 characters, but your
example has more than 7 characters.

 

[SteveDB1]

Hi SSigmaGuy,
I wish it was a constant string. Sorry about the confusion on the 7
characters comment.
I just grabbed the first 6 rows of text out of one book I was working on at
the moment I posted.

Is there a way to look for numbers within a group of strings?
I.e., if I define the macro to look at the first 6 rows for groups of 3 to
12 number strings, how would I perform that task?

And I only say up to 12 numbers, because we'd have a few groupings that'd
look something like xxx_xxx_xxx, or xxx/xxx.x/xxx.x
where x is a number set. And occasionally, instead of a .x, we'd have a
fraction input by use of the alt+188, 189, 190 key combinations.

I've already imported 31 books, and about 10 of them have different
configurations than what I posted for the sample.
I hope this is clearly stated, if not, please ask.

 

[SteveDB1]

Sigma,
I just had another idea, what would it take to extract the numbers out of
the file name?
The file names are always constant, and would have all of the numbers in
them-- without the '/' because that is not allowed in actual names of files.
So we always end up using the underscore, or hyphen in the file name.
I have code already which strips the file extension. So, I just need to
extract the contents of the file name to insert to the sheet name.

Something akin to:

extract file name

nwShtNm1 = extracted/stripped file name

If sheet.name = "Sum" or "SUM" then
nwShtNm = "Sum-" & nwShtNm1

else if sheet.name = "Summary" then
nwShtNm = "Sum-" & nwShtNm1

else if sheet.name = "APN" then
nwShtNm = "APN-" & nwShtNm1

end if

Your thoughts?
Again, thank you for your helps.
Best.

 

[SixSigmaGuy]

Yes, that's very easy.

Function stGetFileName() as String
stGetFileName = ThisWorkbook.Name
End Function

Will get tyou the filename.

Once you've got the filename in a string, you can parse it exactly the same
way as described below.

I'm off sailing for 3 weeks and won't be on the newsgroup until I get back.
I hope this helps you.