+ Reply to Thread
Results 1 to 7 of 7

Text function - first name and last name

  1. 02-08-2020, 07:30 AM #1
    ashishkhurana93
    ashishkhurana93 is offline
    Registered User
    Join Date
    06-04-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    54

    Text function - first name and last name

    I have a column with the data as "First Last". I want to convert it into "FirstLa"
    Reply With QuoteRegister To Reply
  2. 02-08-2020, 07:35 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,626

    Re: Text function - first name and last name

    With your data starting in A1, this in B1 copied down:

    =SUBSTITUTE(LEFT(A1,FIND(" ",A1)+2)," ","")

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    		 Anna Richardson AnnaRi
    2
    		 Peter Jones PeterJo
    3
    		 William Ellis WilliamEl
    4
    		 Arthur Dent ArthurDe
    5
    		 Tilly Barnes TillyBa
    Sheet: Sheet1
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 02-08-2020, 01:00 PM #3
    AlKey
    AlKey is offline
    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: Text function - first name and last name

    A bit shorter formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    v A B
    1 Anna RichardsonAnnaRi AnnaRi
    2 Peter JonesPeterJo PeterJo
    3 William EllisWilliamEl WilliamEl
    4 Arthur DentArthurDe ArthurDe
    5 Tilly BarnesTillyBa TillyBa
    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
    Reply With QuoteRegister To Reply
  4. 02-08-2020, 01:03 PM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,626

    Re: Text function - first name and last name

    Alex - why do you think the data in column A looks like that? The OP said it is First Last, so I don't believe your formula will work.
    Reply With QuoteRegister To Reply
  5. 02-08-2020, 01:20 PM #5
    AlKey
    AlKey is offline
    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: Text function - first name and last name

    Lol! I didn't realized that your original post (is original post)

    Sorry for confusion my apologies

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 02-08-2020, 01:21 PM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,626

    Re: Text function - first name and last name

    I guessed that's what had happened.
    Reply With QuoteRegister To Reply
  7. 02-08-2020, 08:25 PM #7
    hrlngrv
    hrlngrv is offline
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Text function - first name and last name

    Would there ever be middle names? Would you really want James B Kelly, James Fenimore Kellogg and James Kenny all to be JamesKe?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to create a function that will replace specific text in cell a with text fro
    By fozuk1609 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-20-2016, 10:12 AM
  2. Text to Column function (normally used to convert text to number/general format)
    By cks1026 in forum Excel General
    Replies: 1
    Last Post: 04-24-2014, 12:25 AM
  3. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  4. function to return text when first value found but different text for subsequent numbers
    By managingcrap in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2013, 10:52 PM
  5. [SOLVED] function IF to return text based if text contains exact text
    By in nomine noctis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2013, 06:25 AM
  6. 'text to columns' or 'split' function for line of text without space, comma, etc?
    By affj in forum Excel General
    Replies: 4
    Last Post: 07-25-2013, 05:28 AM
  7. Split 1 cell into 2 using delimiter by function not text to columns function
    By robfink2.0 in forum Excel General
    Replies: 3
    Last Post: 10-27-2010, 03:11 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1