Software

Ask something
Discussion Locked

Formatting numbers & text in a cell

0 helpful
5 replies

by bgoldstein

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is there a way to customize the fomatting of a cell containing text and numbers?
Ex: 111X-X1X11-X11XX-1X11X
The number of groupings and characters will always be 4 groups of 5 characters separated by a hyphen.

5 replies

by oldefar

I haven't found a direct answer, but I have a possible solution.

For your entry cell use a column and enter the 20 characters, A for example

Do a Data>Convert Text to Column>Fixed Width at 5, 10, 15, and 20>Output B1>Finish.

Pick a column for the formatted output data. The cells in this column will have the formula =B1&"-"&C1&"-"&D1&"-"&E1 and what you will have is your 111X-X1X11-X11XX-1X11X format.

I suspect you were hoping to do an equivelent of custom numbers (#####"-"#####"-"#####"-"#####) but unfortunately it appears Excel cannot do this with text.

by john_wills

There is available a function hight "format" which takes as parameters the variable you want formatted and a string representing the format desired. Look for the details of that string in Help.

This discussion is closed to new replies.