How to stop a value convert to date after using Text to Columns feature in Excel 2010?

[Jonnywalker]

let's say I have a cell A1 contain 2 values input as (1,3/4), I want to separate the values into other columns like B1 (1) and C1 (3/4) using the Text to Columns delimited feature. However, it keeps showing me as C1(4-March) even though I formatted original cell to text format already.


Anyone has solution to this problem? Thank you guys in advance.

 

[AlKey]

Maybe this

Enter formula in B1 and pull it to the right

[B]=TRIM(MID(SUBSTITUTE(","&SUBSTITUTE($A1,",","),("),",",REPT(" ",25)),25*COLUMNS($A:A),25))[/B]

Excel 2010
	A	B	C
1	(1,3/4)	(1)	(3/4)
Sheet3

 

[Jonnywalker]

Thank you so much, this is very helpful. One thing is it showed me as below:

	A	B	C
1	(1,3/4)	1)	(3/4

<tbody>
</tbody>

Is it possible to get rid of parentheses like the one below? (Sorry I should note that the original cell doesn't contain parentheses.)

	A	B	C
1	1,3/4	1	3/4

<tbody>
</tbody>

 

[AlKey]

Try this one.

[B]=TRIM(MID(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE($A1,"(",""),")",""),",",REPT(" ",25)),25*COLUMNS($A:A),25))[/B]

Excel 2010
	A	B	C
1	(1,3/4)	1	3/4
Sheet3

 

[Jonnywalker]

Thank you so much!!! I think what you provided is very useful. But somehow unable to do some further application(Like Max)

This may belong to a new thread, but is there anyway to pick up largest value for a cell contains multiple values?

Shown as below where B1 is the largest value of A1

	A	B
1	(1,3/4,1/2)	1

<tbody>
</tbody>

​

Originally I was thinking to do "Column to Text" to split the cell and do a Max Function to find the largest number, but have a date format issue. Now with your code, I am able to split it, but unable to use a max function to get the largest value. So I wondering if there is another more direct formula that I can extract the largest value?

 

[Norie]

Why not specify that the column(s) is text on the 3rd step of text-to-columns?

 

[Jonnywalker]

what do you mean by specify? so when I do the text to column, the cell will transform 3/4 as 4-Mar. And if I format the column(s) to text, it will give me some strange number like 41067. That's why I try to avoid using text to column.

 

[Norie]

On the 3rd step of text-to-columns you can specify the format of the columns you are 'importing' in the Column data format section.

If you choose Text for a column then Excel will not change what's in the column

 

[Rick Rothstein]

what do you mean by specify? so when I do the text to column, the cell will transform 3/4 as 4-Mar. And if I format the column(s) to text, it will give me some strange number like 41067. That's why I try to avoid using text to column.

In the third step of Text To Columns, you can select a column (click it) in the table and then click one of the option buttons at the top to tell the dialog box how to handle that column... if you click the Text option button, the selected column will be treated as Text output. If you click the first column and shift-click another, all the cells from the first column you clicked to the second one you clicked with become highlighted and selecting an option button will then apply to all those selected columns all at once.

 

[AlKey]

If you want to get MAX try this array formula

[B]=MAX(IF(ISERROR(("0 "&B1:D1)+0),B1:D1+0,("0 "&B1:D1)+0))[/B]

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Excel 2010
	A	B	C	D	E
1	(1,3/4,1/2)	1	3/4	1/2	1
Sheet3