Assistance with excel macro

[jackharper2548]

Good afternoon,

I need help to develop a macro for a button. I have an SS where i paste in a string of text to cell A1 for example HLX/DBI/123456 Bloggs, Mr Joe

I have a button and macro that removes the forward slash /, the spaces, the comma and full stops and replaces them with an underscore _ as per the below VBA which works perfectly.

<Sub CleanUpfile()

Cells.Select
Range("A1").Activate
Selection.Replace What:="/", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub/>

What i need now is a macro that will copy the cleaned up version of the text for example HLX_DBI_123456_BLOGGS_MR_JOE and create two new versions of this one with L_ at the start and one with INV_ at the start for example

L_HLX_DBI_123456_BLOGGS_MR_JOE
INV_HLX_DBI_123456_BLOGGS_MR_JOE

Can anyone help me work this one out?

 

[dreid1011]

Perhaps this:

Sub CleanUpfile()
With Range("A1")
    .Replace What:="/", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With
Range("A2") = "L_" & Range("A1")
Range("A3") = "INV_" & Range("A1")
End Sub

 

[jackharper2548]

Perhaps this:

Sub CleanUpfile()
With Range("A1")
    .Replace What:="/", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End With
Range("A2") = "L_" & Range("A1")
Range("A3") = "INV_" & Range("A1")
End Sub

Sir! Thank you so very very much with this, this works perfectly and is just what i need.

Many thanks

Jack

 

[dreid1011]

Sir! Thank you so very very much with this, this works perfectly and is just what i need.

Many thanks

Jack

You're welcome, and happy to help.