View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
SixSigmaGuy[_4_] SixSigmaGuy[_4_] is offline
external usenet poster
 
Posts: 20
Default Extract string w/ 7 characters

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.


"SteveDB1" wrote in message
...
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" wrote:

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" wrote in message
...
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.