+ Reply to Thread
Results 1 to 6 of 6

Text to Columns - Formulas

  1. #1
    Registered User
    Join Date
    12-26-2018
    Location
    India
    MS-Off Ver
    2017
    Posts
    17

    Text to Columns - Formulas

    Dear All,

    Good day. I tried taking Text to columns by using RIGHT LEFT LEN MID formula. But unfortunately i'm stuck in between.

    PFA.. Kindly help me on this.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Text to Columns - Formulas

    You are using three different methods.

    How would sa formula know which of the three methods to use?
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,741

    Re: Text to Columns - Formulas

    Using Power Query in the attached file, you can see I achieved the second and third requests. The first column split is close and requires a bit of excel native reformatting after the PQ result.

    Click on my signature Power Query links if you are unfamiliar with Power Query to understand this action.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Text to Columns - Formulas

    Please try at
    B5:G7
    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A5,","," "),"-"," ")," ",REPT(" ",99)),COLUMNS($B5:B5)*99-98,99))

    B14
    =UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"An ",),"The ",),"A ",)," ",),5))

    B20
    =UPPER(TRIM(LEFT(SUBSTITUTE(A20,",",REPT(" ",9)),5)))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-26-2018
    Location
    India
    MS-Off Ver
    2017
    Posts
    17

    Re: Text to Columns - Formulas

    Thank You so much

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,741

    Re: Text to Columns - Formulas

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Copy Formulas from 'Formulas' Sheet into Data Table Columns
    By Jonny757 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-10-2020, 08:18 PM
  2. Make model faster: convert formulas to text and then back to formulas
    By lucassdm7 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-25-2019, 06:49 AM
  3. Text to columns separated by "-" using formulas
    By quocky28 in forum Excel General
    Replies: 2
    Last Post: 03-11-2017, 04:55 AM
  4. VBA Text to Columns wipes out array formulas
    By rschlab1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2014, 05:34 PM
  5. Text to Columns Fed by formulas
    By brendan2711 in forum Excel General
    Replies: 8
    Last Post: 07-13-2013, 08:10 PM
  6. [SOLVED] Exporting columns into new workbook as text values not formulas
    By ryanexceln00b in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-31-2013, 08:37 AM
  7. [SOLVED] I have 2 columns to compare,each text. doing formulas, they don'.
    By Help with Match function & cell formats in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2005, 09:07 PM

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