Macro to find and store the last words in a text string

[rockja]

Hi, I am formatting an exported Excel report from another program using a macro. I need to extract the project name which is at the end of a text string. It needs to be stored as a defined variable to be used and called upon later.

For instance:

Project Name of cell A1 would be = ProjName

The text string is always stored in cell A1 and looks like this:

"Current Solution Summary Report Project: Project Name"; where 'Project Name' will always be different. The the first five words "Current Solution Summary Report Project:" will always be the same.

Thanks!

 

[Blade Hunter]

ProjName = replace(Range("A1").Text,"Current Solution Summary Report Project: ","")

Cheers

Dan

 

[texasalynn]

Welcome to MrExcel board...


try this

Sub projname()
Dim projname As String
projname = Mid(Range("A1"), InStr(Range("A1"), ":") + 2, Len(Range("A1")) - InStr(Range("A1"), ":"))

End Sub

 

[Scott Huish]

Sub test()
Dim ProjName As String
ProjName = Split(Range("A1"), ":")(1)
MsgBox ProjName
End Sub

 

[Blade Hunter]

Sub test()
Dim ProjName As String
ProjName = Split(Range("A1"), ":")(1)
MsgBox ProjName
End Sub

Awesome solution Hotpepper, I never thought of using split .

 

[rockja]

Thanks Blade Hunder/Dan and texasalynn!

Dan, I was just typing a reply saying thank you, the code worked perfectly! texasalynn, I appreciate your solution as well, and thanks for the welcoming to MrExcel Board. This has been a great first experience using forums!

Thanks,
-Jason

 

[texasalynn]

Sub test()
Dim ProjName As String
ProjName = Split(Range("A1"), ":")(1)
MsgBox ProjName
End Sub

very nice!

 

[Scott Huish]

ty

 

[ajlowndes]

or

ProjName = Evaluate("=MID(A1,42,255)")

edit: Whoa I am sure there weren't all those replies a second ago!

 

[Scott Huish]

You're right, that would work too, but you don't need the Evaluate or the 255:

ProjName=Mid(Range("A1"),42)