Pro VBA help needed getting rid of " | " in a string of data

[peter_z]

Hey guys, I have a string of text in a single cell.

name: peter z | name: john h| name: john g| Age: 22

I have some code which seperates the pipes from the cell and then puts it into seperate columns.

Problem: The string Name: John g Age: 22 needs to be together not seperated.

Does anyone have any code that possibly looks at Age and gets rid of the | just before the A ?

Alternatively any suggestions on how I can fix this problem?

Your help is much appreciated!

Thanks

 

[Michael M]

Hi Peter
Before running your seperate code, Highlight your cells and run this to remove the pipes first.

Sub rempipe()
Dim c As Range
For Each c In Selection
    c.Value = Replace(c.Value, "|", " ")
Next c
End Sub

 

[Sektor]

[COLOR="Blue"]Function[/COLOR] RemSep(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\|\s*Age"
        RemSep = .Replace(Str, " Age")
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[peter_z]

Hey Guys, thanks a lot for your time and help
I'm going to fiddle around with the function formula it looks like it will do pretty much what I am looking for.

- Peter