SOLVED

Change names in excel cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2373632%22%20slang%3D%22en-US%22%3EChange%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373632%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20report%20that%20is%20exported%20from%20another%20system%20that%20looks%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%225.20.21.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282411i0FAA84949EE0FEF3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225.20.21.jpg%22%20alt%3D%225.20.21.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20want%20to%20figure%20out%20a%20way%20to%20have%20the%20names%20in%20the%20first%20column%20look%20like%20this%20without%20manually%20changing%20them%20each%20time%20I%20pull%20the%20report%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%225.20.21.2.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282412i0C86F904D99BD72E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225.20.21.2.jpg%22%20alt%3D%225.20.21.2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'm%20sure%20this%20will%20be%20pretty%20easy%20for%20this%20group%20-%20but%20any%20ideas%3F%26nbsp%3B%20I%20was%20hoping%20for%20some%20sort%20of%20formula%20or%20any%20other%20ideas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20this%2C%20I%20need%20to%20format%20the%20rest%20of%20the%20report%20and%20am%20planning%20to%20build%20a%20Macro%20to%20do%20the%20rest%20of%20the%20formatting%20I%20have%20planned...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2373632%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373721%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606460%22%20target%3D%22_blank%22%3E%40NeilKloster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ExtractNames()%0A%20%20%20%20Range(Range(%22A2%22)%2C%20Range(%22A2%22).End(xlDown)).TextToColumns%20_%0A%20%20%20%20%20%20%20%20destination%3A%3DRange(%22A2%22)%2C%20_%0A%20%20%20%20%20%20%20%20Other%3A%3DTrue%2C%20OtherChar%3A%3D%22(%22%2C%20_%0A%20%20%20%20%20%20%20%20FieldInfo%3A%3DArray(Array(1%2C%20xlGeneralFormat)%2C%20Array(2%2C%20xlSkipColumn))%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373885%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373885%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F21903%22%20target%3D%22_blank%22%3E%40hans%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much%20for%20the%20response.%20Perhaps%20I%20did%20something%20wrong%2C%20but%20I%20tried%20this%20and%20it%20only%20removed%20the%20number%20when%20I%20ran%20it%2C%20but%20left%20the%20names%20as%3A%3CBR%20%2F%3ESmith%2C%20John%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20have%20them%20switched%20to%20John%20Smith%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373896%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20it%20looks%20like%20in%20VBA%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22extract.jpg%22%20style%3D%22width%3A%20946px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282428i44A2EC309E0CD993%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22extract.jpg%22%20alt%3D%22extract.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373898%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606460%22%20target%3D%22_blank%22%3E%40NeilKloster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20sorry%2C%20I%20forgot%20to%20change%20the%20order%20of%20first%20name%20and%20last%20name.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ExtractNames()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Dim%20a()%20As%20String%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20For%20Each%20rng%20In%20Range(Range(%22A2%22)%2C%20Range(%22A2%22).End(xlDown))%0A%20%20%20%20%20%20%20%20s%20%3D%20rng.Value%0A%20%20%20%20%20%20%20%20s%20%3D%20Left(s%2C%20InStr(s%2C%20%22(%22)%20-%202)%0A%20%20%20%20%20%20%20%20a%20%3D%20Split(s%2C%20%22%2C%20%22)%0A%20%20%20%20%20%20%20%20rng.Value%20%3D%20a(1)%20%26amp%3B%20%22%20%22%20%26amp%3B%20a(0)%0A%20%20%20%20Next%20rng%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2373970%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20names%20in%20excel%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2373970%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F21903%22%20target%3D%22_blank%22%3E%40hans%3C%2FA%3E%20-%20you%20continue%20to%20amaze%20me!%20Thank%20you%20so%20much%20that%20worked%20perfectly!!%3C%2FLINGO-BODY%3E
Contributor

Hello, 

 

I have a report that is exported from another system that looks like this: 

5.20.21.jpg

 

But I want to figure out a way to have the names in the first column look like this without manually changing them each time I pull the report:  

5.20.21.2.jpg

I'm sure this will be pretty easy for this group - but any ideas?  I was hoping for some sort of formula or any other ideas. 

 

After this, I need to format the rest of the report and am planning to build a Macro to do the rest of the formatting I have planned...

 

 

 

5 Replies

@NeilKloster 

Here is a macro:

Sub ExtractNames()
    Range(Range("A2"), Range("A2").End(xlDown)).TextToColumns _
        destination:=Range("A2"), _
        Other:=True, OtherChar:="(", _
        FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlSkipColumn))
End Sub
@hans

Thank you so much for the response. Perhaps I did something wrong, but I tried this and it only removed the number when I ran it, but left the names as:
Smith, John

Is there a way to have them switched to John Smith?

@Hans Vogelaar 

 

Here is what it looks like in VBA

extract.jpg

@NeilKloster 

I'm sorry, I forgot to change the order of first name and last name.

Sub ExtractNames()
    Dim rng As Range
    Dim s As String
    Dim a() As String
    Application.ScreenUpdating = False
    For Each rng In Range(Range("A2"), Range("A2").End(xlDown))
        s = rng.Value
        s = Left(s, InStr(s, "(") - 2)
        a = Split(s, ", ")
        rng.Value = a(1) & " " & a(0)
    Next rng
    Application.ScreenUpdating = True
End Sub
best response confirmed by NeilKloster (Contributor)
Solution
@hans - you continue to amaze me! Thank you so much that worked perfectly!!