Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

#### Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

#### Feedback

"...I have never been to any technical site that shows concern just to anybody with problems...I look forward to also share in the future..."

#### Geography

Where in the world do Tek-Tips members come from?

# Macro or function

 Forum Search FAQs Links Jobs Whitepapers MVPs
 sterlecki (TechnicalUser) 30 Apr 12 12:53
 I have created a rather complex formula to parse out some text data into a specific format. I would like to be able to store this as a function and/or be able to apply this formula as a macro to a long list using a FOR EACH...NEXT statement.How can I save this formula as either a function or a macro that would allow me to desginate the data cell (A2 in this example) and apply it to any cell I designate?the following is a concatenation of 3 formulas and one text character=LOWER(LEFT(A2,1) & IF(OR(MID(A2,FIND("-",A2)+1,1)="1",MID(A2,FIND("-",A2)+1,1)="2",MID(A2,FIND("-",A2)+1,1)="3"),MID(A2,FIND("-",A2)+1,2),CONCATENATE("0",MID(A2,FIND("-",A2)+1,1))))&"_"&LOWER(IF(MID(A2,FIND("-",A2)-4,1)=" ",CONCATENATE("0",MID(A2,FIND("-",A2)-3,3)),MID(A2,FIND("-",A2)-4,4)))The data looks like this:mesa unit 5a1-6dMesa 15D4-8Mesa Unit 15A3-8Stewart Point 9C2-8Mesa 9D3-17Results Look like this:m06_05a1m08_15d4m08_15a3s08_9c2m17_09d3Perhaps I need both a function and then I can apply that function as a macro to any column I choose.thanks
 SkipVought (Programmer) 30 Apr 12 21:59
 hi,Why would this m06_05a1m08_15d4m08_15a3s08_9c2m17_09d3not be s08_09c2  Skip,Just traded in my old subtlety...for a NUANCE!
 SkipVought (Programmer) 30 Apr 12 22:02

here's a first shot, using testit as a function right on the worksheet...

#### CODE

Function testit(rng As Range) As String
Dim a

a = Split(rng, " ")

testit = Left(a(0), 1)

testit = testit & Format(RemAlpha(CStr(Split(a(UBound(a)), "-")(1))), "00") & "_" & Split(a(UBound(a)), "-")(0)
End Function
Function RemAlpha(strS As String)
':remove ALPHA from a string
Dim re As Object ' object to hold Regular Expression object

Set re = CreateObject("VBScript.RegExp") ' late bind to RegExp object so no need to reference in application

With re
.Global = True ' find all matches not just first
.MultiLine = True ' over multiple lines
.IgnoreCase = True ' whether upper or lower case (more relevant for alpha char matching)
.Pattern = "[A-Z]" ' regular expression for numeric range
RemAlpha = .Replace(strS, "") ' set return value to value of strS where everything matched by the pattern is replaced with ""
End With

End Function

Skip,

Just traded in my old subtlety...
for a NUANCE!

 N1GHTEYES (TechnicalUser) 1 May 12 4:42
 If you want to be able to apply the formula to any cell from any cell, then you'll need to follow Skip's example and create a function in VBA.However, if you're happy for the output always to be, say, one cell to the right of the input cell (or any other fixed positional relationship), you could simply name the formula.  In other words, put the formula in cell B2, and copy the text of the formula to the clipboard.  The click "Insert", "Name", "Define" via the menu, and in the dialog box, call it MyFunc (or whatever you want) and paste the function into the formula bar of the dialog box, and save it.Then, if you type "= MyFunc" into cell B3, it will apply the function to cell A3.Tony
 sterlecki (TechnicalUser) 2 May 12 14:53
 N1GHTEYES (TechnicalUser) 4 May 12 10:40

#### Quote:

Can named formulas be accessible from other workbooks similar to the personal macro workbook

I'm not sure.  I never use personal.xls.  However, I think it should work.  Try it and see.

Tony
 SkipVought (Programmer) 7 May 12 15:13

Modify testit as posted...

#### CODE

Function testit(rng As Range)
Dim a, s As String, byt As String, i As Integer, sOUT As String

a = Split(rng, " ")

testit = Left(a(0), 1)

s = Split(a(UBound(a)), "-")(0)

testit = testit & Format(RemAlpha(CStr(Split(a(UBound(a)), "-")(1))), "00") & "_"

For i = 1 To Len(s)
byt = Mid(s, i, 1)

Select Case byt
Case "0" To "9"
sOUT = sOUT & byt
Case Else
Exit For
End Select
Next

testit = testit & Format(sOUT, "00") & Right(s, Len(s) - i + 1)

End Function

Skip,

Just traded in my old subtlety...
for a NUANCE!

 sterlecki (TechnicalUser) 10 May 12 17:27

Thanks alot Skip the new version worked like a charm. I made one modification to force the alpha characters to lower case.

#### CODE

Function testit(rng As Range)
Dim a, s As String, byt As String, i As Integer, sOUT As String
a = Split(rng, " ")
testit = Left(a(0), 1)
s = Split(a(UBound(a)), "-")(0)
testit = testit & Format(RemAlpha(CStr(Split(a(UBound(a)), "-")(1))), "00") & "_"
For i = 1 To Len(s)
byt = Mid(s, i, 1)
Select Case byt
Case "0" To "9"
sOUT = sOUT & byt
Case Else
Exit For
End Select
Next
testit = LCase(testit & Format(sOUT, "00") & Right(s, Len(s) - i + 1))
End Function

thanks so much for your efforts

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!