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.

 

[Jonnywalker]

Norie
yea I tried that, but it still give me the date format (4-Mar) which I don't want

 

[Norie]

What exactly did you try?

Using that option will definitely not convert your data to dates.

Did you make sure to apply it all the relevant columns?

 

[Jonnywalker]

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.

Funny thing is, even if I select text format on the third step of Text to Column, it still give me a date format...

 

[Jonnywalker]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

I apologize I am pretty excel illiterate. When I copy and paste the code into B1 using CTRL+SHIFT+ENTER, it all return me with 0

 

[Rick Rothstein]

Funny thing is, even if I select text format on the third step of Text to Column, it still give me a date format...

If you select the correct column or columns, that is not possible (unless, possibly, if you have some VBA event code overriding things).

 

[Jonnywalker]

What exactly did you try?

Using that option will definitely not convert your data to dates.

Did you make sure to apply it all the relevant columns?

sorry if it is a little bit misleading. Excel automatically convert my fraction value into date format. I tried the third step which export the data with Text option, which did not help, I am still getting the date format.

 

[AlKey]

I don't know why this is so difficult. I have no issues getting max values.

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

 

[Norie]

When are you actually using Data>Text to columns...?

Are you using it or are you just opening text files?