Maintain final zeros in text to columns

[AmyK023]

HI,
MS Excel 2010

I have the text strings below (900+ in total).
The final numerical column represents the version number where in 3.1 is "three-point-one" and 3.10 is "three-point-ten".

R^11-111111^Some Document^1.40 (one-point-forty)[TABLE="width: 643"]
<TBODY>[TR]
[TD]R^22-222222^Another Document^1.4 (one-point-four)
R^A3333^Different document ^3.0 (three-point-zero)</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[TABLE="width: 643"]
<TBODY>[TR]
[TD]
Within a macro to create a user document I use text to columns, ^-delimited, to separate the the information.

After the split, I lose the last zero, regardless of how I format the recipient column. While there is no issue losing the zero on 3.0, I need to maintain the zero in 1.40. I'm not sorting by this column.

So far I have used conditional formatting [ value(right(cell, 1))= 0 ], to highlight the affected strings and added an appostrophe as part of the text string (e.g. ...^'1.40), but I can't find a way to remove the apostrophe and maintain the final zero.

The both the content and number of the text string are dynamic making the manual method described above less and less desirable.

Any suggestions on maintaining the final zero?[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[adam087]

You can force Text-to-Columns to keep that field as text which does as you require. I got the appropriate code using Macro Recorder, and the help file explained it in some excellent detail this time; but if you post the code you're using I'm sure we can have a look at it.

Or you could change the Number Forma to show two Decimal Places. Which will display as you require. I think.

Any of that stream-of-conciousness help?

/AJ

 

[AmyK023]

You can force Text-to-Columns to keep that field as text which does as you require. I got the appropriate code using Macro Recorder, and the help file explained it in some excellent detail this time; but if you post the code you're using I'm sure we can have a look at it.

::head thump:: Should have had a V8. I didn't realize that the array() contained various parameters (VBA neophyte) and didn't check the text qualifier when using the macro recorder. Array (4, 1) v. Array (4, 2). So, yes, forcing Text-to-Columns to see the last array as text solved the problem.

Or you could change the Number Forma to show two Decimal Places. Which will display as you require. I think.

Forcing it to two decimal places doesn't work as 3.1 (three point one) is different than 3.10 (three point ten)

Any of that stream-of-conciousness help?

Yes, Thank you.

 

[adam087]

Forcing it to two decimal places doesn't work as 3.1 (three point one) is different than 3.10 (three point ten)

Oh yeah. Should have thought of that.

Either way. Glad it worked. Thanks for the feedback.

/AJ