+ Reply to Thread
Results 1 to 8 of 8

Rearranging text based on spaces and comma

  1. #1
    Registered User
    Join Date
    03-22-2016
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Rearranging text based on spaces and comma

    I have two reports that have employee names in different formats.
    File 1 is last comma first; file 2 is first space last.
    Some have middle initials; some have two first names; some have two last names.

    I have a macro set up to use a helper column and make each file first pipe last.
    When I wrote the inital macro, I did not take into account two last names (initial data did not have any).
    Now I'm stumped on how to extract the second last name and first name in the comma file.

    Currently:
    File 1 has DELA CRUZ, DAVID. Macro creates CRUZ,|DELA
    File 2 has DAVID DELA CRUZ. Macro creates DAVID|CRUZ

    File 2 is fine; I need to get file 1 to do the same.

    Current formula for file 1 is
    =IF(IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)=2,MID(SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))),FIND("^",SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2," ",""))))+1,256)&"|"&LEFT(C2,FIND(" ",C2)-1),IF(IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)=3,MID(MID(MID(SUBSTITUTE(C2," ","^",1),1,256),FIND("^",SUBSTITUTE(C2," ","^",1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(C2," ","^",1),1,256),FIND("^",SUBSTITUTE(C2," ","^",1)),256))-2)&"|"&LEFT(C2,FIND(" ",C2)-1),""))

    Basically, if C2 has two words, give me the last word pipe then first word. If C2 has three words, give me the second word pipe then first word.
    I think I need it to evaluate where the comma is.
    If there are three words and two are after comma, give me second word pipe then first word.
    If there are three words and two are before comma, give me last word pipe then second word.
    I'm just not sure how to add that comma stipulation in. This one is already fairly complicated for me.

    After this step completes, the macro adds another helper column and removes the comma, so that does not need to be included in this step.

    Thanks in advance!

  2. #2
    Forum Contributor shivya's Avatar
    Join Date
    08-19-2017
    Location
    Delhi, India
    MS-Off Ver
    2013
    Posts
    240

    Re: Rearranging text based on spaces and comma

    Please attach sheet.
    To attach worksheet Go advanced -> Manage attachments -> Choose file -> Upload
    Don't forget to Mention your desired result in the sheet..
    There should be sample data only, a lot of data creates confusion.

    Thanks & Regards

    Shivya

    http://excelvbatipsforbeginners.blogspot.in/

  3. #3
    Registered User
    Join Date
    03-22-2016
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Re: Rearranging text based on spaces and comma

    Sheet attached.
    Two tabs, each representing File 1 and File 2 in original request.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Rearranging text based on spaces and comma

    UDF

    Please Login or Register  to view this content.
    =match_Names(B2)

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Rearranging text based on spaces and comma

    Here is without helper columns
    Enter formula in C2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C
    1 Type Employee
    2 RT APOLINARIO, PHIL ROBERT PHIL|APOLINARIO
    3 RT CANO, WENDY WENDY|CANO
    4 RT CAO, ANH ANH|CAO
    5 RT DELA CRUZ, ROBERT CRUZ|DELA
    6 RT DIGHE, ASHWIN ASHWIN|DIGHE
    7 RT EBREO, JONATHAN JONATHAN|EBREO
    8 RT GARCIA, CASANOVA CASANOVA|GARCIA
    9 RT GWOZDZ, JOSEPH JOSEPH|GWOZDZ
    10 RT HO, DUYEN DUYEN|HO
    11 RT KHAIRZADAH, BASHIR BASHIR|KHAIRZADAH
    12 RT LE, KIET KIET|LE
    13 RT LOCQUIAO, LYDIA LYDIA|LOCQUIAO
    14 RT MAYO, ALEXANDER ALEXANDER|MAYO
    15 RT MEJIA, MARIA LOUVINIA MARIA|MEJIA
    16 RT PHAN, TUU TUU|PHAN
    17 RT QUINTILLA, NERI NERI|QUINTILLA
    18 RT RAMIREZ, RENE RENE|RAMIREZ
    19 RT REID, ANDREW ANDREW|REID
    20 RT ROBLETO, JEFF JEFF|ROBLETO
    21 RT SHEIN, THAN THAN|SHEIN
    22 RT SINGH, MANVIR MANVIR|SINGH
    23 RT YELLAMELLI, DAMARIS DAMARIS|YELLAMELLI
    Last edited by AlKey; 11-26-2017 at 12:56 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    03-22-2016
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Re: Rearranging text based on spaces and comma

    John,
    Thank you - this works perfectly!
    Could I trouble you to explain how it does what it does? I don't understand any of it!
    Thanks, Darcie

  7. #7
    Registered User
    Join Date
    03-22-2016
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Re: Rearranging text based on spaces and comma

    @AlKey - the formula didn't do what I needed, but thanks anyway!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Rearranging text based on spaces and comma

    I modified the code (now shorter) and added comments but do check it out.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace any number of spaces with comma
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-24-2017, 07:47 AM
  2. Sumif based on filter with comma (text)
    By yogananda.muthaiah in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-26-2016, 02:05 PM
  3. [SOLVED] Remove consecutive spaces and replace with single comma
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-11-2015, 05:20 PM
  4. [SOLVED] Getting info from Excel to be comma delimited with no spaces
    By himay in forum Excel General
    Replies: 3
    Last Post: 07-10-2014, 11:18 PM
  5. [SOLVED] Remove spaces and replace with comma.
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2013, 01:01 PM
  6. Find and remove all text to the left of last comma (including comma) in a cell
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-05-2013, 08:47 AM
  7. Removing spaces and adding comma's using a Macro
    By Jeroen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2006, 05:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1