+ Reply to Thread
Results 1 to 5 of 5

How to split text in a cell

  1. #1
    Registered User
    Join Date
    03-02-2020
    Location
    California
    MS-Off Ver
    MS 365 Sub Version 16.57 (Mac)
    Posts
    37

    How to split text in a cell

    Hello,

    I'm hoping the should be a simple fix for someone and that no attachment is needed.

    I have 5 names that all have a number right in front of their name:

    1Jayme
    14Henry
    3Susan
    5Harry
    12Bob

    I just need a formula to remove the numbers before the name so they can look like this:

    Jayme
    Henry
    Susan
    Harry
    Bob

    Thanks for your help!

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: How to split text in a cell

    Assum A1 is text string

    =IFERROR(MID(A1,AGGREGATE(14,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)+1,255),A1)
    Quang PT

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: How to split text in a cell

    Or if your excel is actual Ver 10, try this instead:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-02-2020
    Location
    California
    MS-Off Ver
    MS 365 Sub Version 16.57 (Mac)
    Posts
    37

    Re: How to split text in a cell

    This works great. By the off chance, would you know the formula you would use if I needed to transfer this to google sheet? I am transferring a project to someone that doesn't have excel and apparently google sheets does not have the aggregate function. This works perfect for me, though. Thank you greatly!

    Quote Originally Posted by bebo021999 View Post
    Assum A1 is text string

    =IFERROR(MID(A1,AGGREGATE(14,6,SEARCH({0,1,2,3,4,5,6,7,8,9},A1),1)+1,255),A1)

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: How to split text in a cell

    Does #3 work for Google sheet? I don't know much about Google sheet.

+ 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] Split cell text
    By Rafaelpresa123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-11-2020, 12:11 PM
  2. [SOLVED] Split text from cell
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 09-25-2018, 01:27 AM
  3. Too Much text in Cell!! Split using VBA
    By mnadamn19 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2017, 10:21 PM
  4. [SOLVED] Text in a Split Cell
    By Pete291 in forum Excel General
    Replies: 4
    Last Post: 12-02-2016, 12:36 PM
  5. Replies: 4
    Last Post: 11-11-2015, 03:00 PM
  6. Split text into different cell
    By Allmaths in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-25-2015, 06:03 AM
  7. [SOLVED] Split text in a cell
    By dougmorgan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-23-2013, 12:48 PM

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