CODEWITHSUNDEEP
excelexcel-formula
hjh93
hjh93

Reputation: 570

Excel formula - How to separate a formula value in a cell into 2 different cells?

In my Excel sheet I have a column which gets the transposed data from a row of a different sheet.

=TRANSPOSE(DATA!L170:CC170)

An example of a cell's data is 0/300 and is consistent throughout the column.

Now I plan to have 2 new columns next to the first column to show the numbers separate:

| 0/300 | 0 | 300 |

I want the 2 columns to show their values as soon as there is data on the first column.

At first I wanted to try out the built in text to column function. But apparently it doesn't work because it only detects the formula and not the value.

Is there any other formula that can do what I want? A macro would certainly work but I'm also looking for a more simpler method if there is one.

Upvotes: 1

Views: 482

Answers (2)

Error 1004
Error 1004

Reputation: 8220

There are two methods you could try:

Method 1

Formula for Column A:

=IFERROR(LEFT(A1,FIND("/",A1)-1),"")

Formula for Column B:

=IFERROR(MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1)),"")

Results:

enter image description here

Method 2

  • Select your Data
  • Go to Data
  • Data Tools tab
  • Text to Columns
  • Delimited - Next
  • Other: import "/" in the box - Next
  • Finish

Results:

enter image description here

Upvotes: 1

virolino
virolino

Reputation: 2201

That might be quite difficult to achieve. I guess, impossible.

Why? Because the TRANSPOSE function returns a range of a very exact size, without room to add new columns inside.

Workaround:

Add the extra-columns at the right, beyond the end of the range. Just use the LEFT / MID / RIGHT functions to get the fragments from the original text.

The workaround will work if the size of the range does not change, and if the contents are always the same in the same positions. If you want a (highly) adaptive formula, you will need to go into VBA for Excel, and that's going to be "uglier".

Edit: I took it for granted, but @MitchellDeane has a good point:

Note that you can use FIND in your left functions in case of different sizes of inputs: =LEFT(A1,(FIND("/",A1,1)-1)). Such as 0/300, 0/3000, 30/30000

Upvotes: 3

Related Questions