Text to Columns Fed by formulas

**brendan2711** · 07-13-2013, 07:51 AM

Hey guys

I am Currently trying to add up some specific things in a spreadsheet, but in order to do so, I need to first separate the number from the type into two different columns (text to columns feature). However, the cells I wish to do this to are fed by formulas (lookups) etc, and so when I try to use text to columns, it gives me the option of separating the formula rather then the result. Can I get it to let me separate the result instead?

Cheers.

**martindwilson** · 07-13-2013, 08:15 AM

no ! copy the results /paste somewhere else as special/values then use text to columns on that

**brendan2711** · 07-13-2013, 08:29 AM

Originally Posted by martindwilson

no ! copy the results /paste somewhere else as special/values then use text to columns on that

It won't update the cells I paste it in when the formula changes what is in the cell though. Can I do this so it will update as the formula gives different results?

**martindwilson** · 07-13-2013, 08:38 AM

well you could probably split the data with formulas to give the desired result then what is you want to split?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**brendan2711** · 07-13-2013, 08:52 AM

Basically there are a bunch of cells lets say A3, A6 & A9, which are fed by a this formula
=IF($AD2="","",VLOOKUP($AD2,Sheet2!$X$1:$AD$63,7,FALSE))

The result comes up with a number and a colour, for example, 2 Blue, 3 Purple etc.

What I am trying to do is to separate the number from the colour, so that I can add the numbers for each colour together (2 Blue & 3 Blue would give 5 Blue), in another cell which is currently =(A3&A6&A9), But I was going to change once I figured out this part...

The Spreadsheet itself is rather complicated, So I wont post it here, if I am not making myself clear I may try to mock something up for it when I get a chance.

**Norie** · 07-13-2013, 09:05 AM

Why not seperate the number and colour in the source data?

**martindwilson** · 07-13-2013, 09:20 AM

if its as simple data as you say

**newdoverman** · 07-13-2013, 10:24 AM

Sorry Martindwilson. I accidentally downloaded your file and made changes.....didn't look at the author....too early in the morning for this old guy. Please accept my apology.

This should do what you want:

**brendan2711** · 07-13-2013, 08:10 PM

Thanks guys, Was exactly what I needed. You have no idea how long I sat here trying to figure that one out. Cant believe it was so simple. Haha.
Thanks again guys. Really appreciate it!