Excel Formula Help

Discussion in 'Mac Apps and Mac App Store' started by EricBrian, Jan 15, 2009.

  1. EricBrian macrumors 6502a

    EricBrian

    Joined:
    Jul 30, 2005
    #1
    So, I really don't want to learn Excel so was wondering if somebody could help.... I need to rewrite the following:

    Eric Brian
    William John Smith

    These should be rewritten into:

    Brian, Eric
    Smith, William John

    Can somebody who knows Excel help with a formula for this?

    Thanks
     
  2. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #2
    So, do you enter "Eric Brian" in one cell and want to have "Brian, Eric" in another? Assuming that it's always the same form (i.e. no middle names), you can use a formula like that (assuming the original content is in A1):

    =CONCATENATE(RIGHT(A1;LEN(A1)-FIND(" ";A1));", ";LEFT(A1;FIND(" ";A1)-1))

    I'm not sure about the English formula names as I use the German version, but it should help you nevertheless.
     
  3. EricBrian thread starter macrumors 6502a

    EricBrian

    Joined:
    Jul 30, 2005
    #3
    Oh, I forgot to mention that this is Excel for mac and not windows. Does this matter? I ask because this does not work. Also, "Eric Brian" is in one cell.

    Thanks! :)
     
  4. EricBrian thread starter macrumors 6502a

    EricBrian

    Joined:
    Jul 30, 2005
    #4
    I modified that formula somewhat and came up with this:

    =RIGHT(E1,LEN(E1)-FIND(" ",E1)) & ", " & LEFT(E1,FIND(" ",E1)-1)

    It worked nicely.

    Thank you very much! :)
     
  5. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #5
    Ok, so it seems Excel for Mac doesn't know the concatenate function and uses "," instead of ";". Glad you found a way around it and it works. :)
     
  6. EricBrian thread starter macrumors 6502a

    EricBrian

    Joined:
    Jul 30, 2005
    #6
    Veldek, one more thing... Sometime I only have a first name so now I get a #VALUE! error. Is there a way to avoid this? Thanks.
     
  7. miles01110 macrumors Core

    miles01110

    Joined:
    Jul 24, 2006
    Location:
    The Ivory Tower (I'm not coming down)
    #7
    I would just do it in steps.

    Do "Text to Columns" on your data with a space as the delimiter.

    Then to get the names in reverse order, do something like

    =IF(ISBLANK(B1)=TRUE, A1, CONCATENATE(B1, ", ",A1))

    So if the second cell (the last name) is empty then it just puts the first name. If there is a string in each cell then it will format your text how you want it. This will also avoid the #VALUE error you get.
     
  8. Veldek macrumors 68000

    Veldek

    Joined:
    Mar 29, 2003
    Location:
    Germany
    #8
    You can try to use a function that gives you a number value for an error you you receive. My translation of this function would be ERROR.TYPE. It's the FIND formula which can't find a blank and then returns this error. Use an IF function to see if the FIND function gives you an error and control if you can use this formula or another one when there's only one given name.
     

Share This Page