data from a cell (string)

[robcarr]

Dear All,

I have a cell with the following info in,

(London here Team Manager) Carr, Rob Rhino (Myself)

I need to make the data above look like this

Rob Carr

basically I need to remove all non required stuff and enter data firstname surname



Thanks Rob.

 

[robcarr]

i have done this so far, it works, but I was wondering if there is an easier way to do this.

Application.DisplayAlerts = False
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=")", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
Selection.Delete Shift:=xlToLeft
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Columns("B:B").Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1))
Columns("B:B").Delete Shift:=xlToLeft
Columns("C:C").ClearContents
Range("C1").Value = "=CONCATENATE(RC[-1],RC[-2])"
Range("C1").AutoFill Destination:=Range("C1:C71")
Columns("C:C").EntireColumn.AutoFit
Range("C1").Select
Application.DisplayAlerts = True

Thanks Rob.

 

[xlbo]

Rob - almost certainly so - whether there is or not depends pretty much on the consistency of the data

a: is there always a ) just before the surname
b: is there always a , between the surname and forename
c: is there always one space between the forename and the next bit of text
d: is there always a space after the , that seperates the surname and forename ??


This will still require a bit of code as there are several text positions that will need to be obtained, based on what precedes them

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[nnett]

Hi robcarr

If the format is fixed, the following will give the same results as above, the code uses the InStr function to find the first “)” the “,” and the space after the first name.

    Dim WholeString As String
    Dim FirstClose As Integer
    Dim FirstComma As Integer
    Dim SecondSpace As Integer
    Dim CorrectedName As String
    
    WholeString = ActiveCell.Text
    FirstClose = InStr(1, WholeString, ")", 1)
    FirstComma = InStr(FirstClose, WholeString, ",", 1)
    SecondSpace = InStr(FirstComma + 2, WholeString, " ", 1)
    CorrectedName = Mid(WholeString, FirstComma + 2, _
        SecondSpace - (FirstComma + 2)) & " " & Mid _
        (WholeString, FirstClose + 2, FirstComma - _
        (FirstClose + 2))
    Range("C1").Select
    ActiveCell.Value = CorrectedName

Hope this helps

Steve

 

[TonyJollans]

Hi Rob,

Assuming, as others have, that the format is fixed as shown in your post. Just for fun, the (code for the) formula approach ..

[blue]Columns("B:B").Select
Selection.FormulaR1C1 = "=LEFT(TRIM(MID(RC[-1],FIND("","",RC[-1],FIND("")"",RC[-1])+1)+1,256)),FIND("" "",TRIM(MID(RC[-1],FIND("","",RC[-1],FIND("")"",RC[-1])+1)+1,256)))) & TRIM(MID(RC[-1],FIND("")"",RC[-1])+1,FIND("","",RC[-1],FIND("")"",RC[-1])+1)-FIND("")"",RC[-1])-1))"
Selection.Copy
Selection.PasteSpecial xlValues
Application.CutCopyMode = False
Columns("A:A").Delete[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[robcarr]

thanks for your responses on this

Geoff,

The data isnt always the same the names are fed into another system and I get the data exported into Excel, so it has human error, but 99% of the time a,b,c and d are as you say.
I havent had time to check coding yet.

rob.

Thanks Rob.

 

[xlbo]

Rob - given that - nnet's code should work nicely - you just need to make it loop...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question