Using Text-to-column in a formula

[picard359]

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

[NBVC]

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.

[picard359]

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 ]

[NBVC]

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))

[JeffreyWeir]

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)),"")