Results 1 to 5 of 5

Thread: Using Text-to-column in a formula

  1. #1

    Post Using Text-to-column in a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    First Post..... so be gentle!

    Cell A1 =1+2+3

    Can I programatically expand that into columns such that
    B1 is 1 C1 is 2 D1 is 3

    I've tried using =LEFT forumla but that seems to be working on the value of A1 and not on the formula.

    Cheers for your help.
    PiCaRd359

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    You can't do it with any built in excel functions...

    But you can use this method... which kinda feels like an Excel function...

    Go to Formula tab, then select Define Name in the Defined Names section. Enter the word Formula in Name field, and enter formula in the Refers to field: =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,-1))

    Click Ok.

    Now, assuming you can have operators, +, -, * and / place this formula in cell directly to the right of cell you want to evaluate:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Formula,"=",""),"+"," "),"-"," "),"*"," "),"/"," ")

    This should repace all the operators including the = with a space.

    in next cell over enter formula:

    =IFERROR(0+TRIM(MID(SUBSTITUTE($B1," ",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100)),"")

    where $B1 is cell you put the first formula in... and copy across as far as you need.... you can then copy down if you have more...

    NOTE: You will need to enable macros for this to work. It uses older XL4 MACRO to work.


  3. #3
    Quote Originally Posted by NBVC View Post
    You can't do it with any built in excel functions...

    But you can use this method... which kinda feels like an Excel function...

    You, Sir, are a Gentleman and a Scholar!!

    Thanks for that. I have another query to follow this.

    The cells where the original value (=1+2+3) are stored form a table that cannot be disturbed. As in, I can't be inserting columns there.

    Is there a way to 'replicate' this cell in another worksheet? I tried 'Paste Link' but your instructions do not work on that cell as that cell is [ =Sheet1!A1 ] not [ =1+2+3 ]

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    I don't think it will work looking in another sheet, as the Get.Cell formula is particular about the location of the target cell being offset from where the function is placed, and that means being in the same sheet.

    You can change the rows and columns parameters in the offset function in =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,-1)) to adjust for how far from the cell you enter the =Substitute() formula that the target cell is.

    Ie.. if the target is in column A, and you want the formula in Column Z, then you would use =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,-25))


  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Very clever NBVC. A couple of revisions I would suggest:
    Instead of =GET.CELL(41,OFFSET(INDIRECT("RC",FALSE),0,-25)) use a relative reference to do away with that volitile INDIRECT. i.e. if your formula was in row A1 you could select B1 and then define this name:
    =GET.CELL(41,Sheet1!A1)
    ...which then would always point to the cell to the immediate left.

    Also, I'd suggest using the Pipe character | instead of spaces, as spaces can quite legitimately appear between numbers/text in formulas (especially if those formulas use Excel's table notation). So your excellent formulas would be amended like this:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Formula,"=",""),"+","|"),"-","|"),"*","|"),"/","|")
    and this:
    =IFERROR(0+TRIM(MID(SUBSTITUTE($C1,"|",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100)),"")

Posting Permissions

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