General discussion

Locked

Formatting numbers & text in a cell

By bgoldstein ·
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.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Oldefar In reply to Formatting numbers & text ...

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.

Collapse -

by bgoldstein In reply to

Poster rated this answer.

Collapse -

by john_wills In reply to Formatting numbers & text ...

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.

Collapse -

by bgoldstein In reply to

Poster rated this answer.

Collapse -

by bgoldstein In reply to Formatting numbers & text ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums