[Solved] How to format a text entry

[purplekegler]

I am not even sure if this is possible to do with text, but what I would like to do is type in something like QH10S in a cell and have it formatted as Qh 10s. Is this possible? I want to to make the letters C,D,H, and S small case and then add a space afterwards. If anyone knows how this can be done it would be much appreciated. Thanks!

[acknak]

There's no way to do it as you enter the data--without some macro code anyway--but you can easily change them in bulk after you enter the data. You could also enter the data in one cell, but have a formula in another cell that displays the entry with the desired adjustments.

[purplekegler]

There's no way to do it as you enter the data--without some macro code anyway--but you can easily change them in bulk after you enter the data. You could also enter the data in one cell, but have a formula in another cell that displays the entry with the desired adjustments.

OK, that is kind of what I thought. I was hoping that there would be a way of formating it in the way that number entry works. For example, if you have a cell formatted to have two digits after the decimal and type in 25, once you leave that cell it becomes 25.00. That said, you mentioned two things... macro code, and formula in a different cell... could you elaborate please? Thanks!

[Villeroy]

Just nitpicking: You want to convert a text value to another text value. This has nothing to do with formatting.

qh10s, QH10S => Qh 10s (first letter upper case, second lower case, space at position #3, rest lower case)
Having the value in A1:

Code: Select all

=PROPER(LEFT($A1;2))&" "&LOWER(RIGHT($A1;LEN($A1)-2))

should do the trick. See help on text functions PROPER, UPPER, LOWER, LEFT, RIGHT, MID, LEN, MID,...

[Dave]

A program called CSVed will enable this readily on whole columns after the fact, but it will require a few logical steps, and changes will be permanent [unless changed back similarly, or saved as another filename.] Perhaps not worth it unless the number of row entries is large.

David.

[purplekegler]

Just nitpicking: You want to convert a text value to another text value. This has nothing to do with formatting.

qh10s, QH10S => Qh 10s (first letter upper case, second lower case, space at position #3, rest lower case)
Having the value in A1:

Code: Select all

=PROPER(LEFT($A1;2))&" "&LOWER(RIGHT($A1;LEN($A1)-2))

should do the trick. See help on text functions PROPER, UPPER, LOWER, LEFT, RIGHT, MID, LEN, MID,...

Thanks for pointing me in the right direction. Here is the following function I came up with:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(“QH10S”);”S”;”s “);”H”;”h “);”D”;”d “);”C”;”c “)

(I added the UPPER function so that it would work regardless of how it is entered)

Now all I need to figure out is how to replace the "QH10S" with the contents of a cell. I'd really like to have the data in the current cell converted. Any ideas?

[Villeroy]

Now all I need to figure out is how to replace the "QH10S" with the contents of a cell. I'd really like to have the data in the current cell converted. Any ideas?

Having the values in A1:A999, put your formula into any free cell, replace literal “QH10S”with unquoted reference A1, copy down the formula cell (copy/paste or drag the tiny square at bottom-right cell corner) until it convers all values in A1:A999. Then copy the resulting range of cells, select the first target cell (A1) and paste special (Ctrl+Shift+V) text values only without formulas.

[purplekegler]

Now all I need to figure out is how to replace the "QH10S" with the contents of a cell. I'd really like to have the data in the current cell converted. Any ideas?

Having the values in A1:A999, put your formula into any free cell, replace literal “QH10S”with unquoted reference A1, copy down the formula cell (copy/paste or drag the tiny square at bottom-right cell corner) until it convers all values in A1:A999. Then copy the resulting range of cells, select the first target cell (A1) and paste special (Ctrl+Shift+V) text values only without formulas.

Thank you again!

What I did was to create a Sheet 2 identical to Sheet 1. I will enter my data on Sheet 2 and get the desired results on Sheet 1 by using the following function:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(Sheet2.A2);"S";"s ");"H";"h ");"D";"d ");"C";"c ")

I tried it and it works flawlessly! Thanks!