Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number formatin XL 2000: mixing alph.chars.& numbers
Hello everyone,
Case: I enter a mobile (dutch) mobile phonenumber (06 12345678) into a cell that has no number formatting setting it looks like this 612345678. I can make ik look like this: 06 12 345 678 (this is what it reads like when checking the invoice form the phone company). Problem: I want to make a number format (in the first instance I'd like to try it without using VBA) which makes a dutch car license plate with these characters :02GDTT (example) look like 02 GD TT. Unfortunately I don't know how to do this using the regular excel dialog for numberformatting because I don't know which character represents an alphabetic character. TIA Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
number formatin XL 2000: mixing alph.chars.& numbers
Hi Peter,
=LEFT(A1,2)&" "&MID(A1,3,2)&" "&RIGHT(A1,2) -- Kind Regards, Niek Otten Microsoft MVP - Excel "Peter" <****************** wrote in message ... Hello everyone, Case: I enter a mobile (dutch) mobile phonenumber (06 12345678) into a cell that has no number formatting setting it looks like this 612345678. I can make ik look like this: 06 12 345 678 (this is what it reads like when checking the invoice form the phone company). Problem: I want to make a number format (in the first instance I'd like to try it without using VBA) which makes a dutch car license plate with these characters :02GDTT (example) look like 02 GD TT. Unfortunately I don't know how to do this using the regular excel dialog for numberformatting because I don't know which character represents an alphabetic character. TIA Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
number formatin XL 2000: mixing alph.chars.& numbers
On Sat, 16 Oct 2004 20:00:33 +0200, "Peter" <****************** wrote:
Problem: I want to make a number format (in the first instance I'd like to try it without using VBA) which makes a dutch car license plate with these characters :02GDTT (example) look like 02 GD TT. Unfortunately I don't know how to do this using the regular excel dialog for numberformatting because I don't know which character represents an alphabetic character. I do not believe it is possible without either a formula displaying the result in another cell, or by using VBA. Since you don't want to use VBA, you could put a formula in an adjacent column: =LEFT(A1,2)&" "&MID(A1,3,2)&" "&RIGHT(A1,2) Even with VBA, you would not be able to DISPLAY 02GDTT as 02 GD TT. You would have to turn it into the string "02 GD TT" in order to display it in a worksheet cell. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
number formatin XL 2000: mixing alph.chars.& numbers
You can't format (format=Cell=Number Tab) a text string (or alpha numeric
value which is also a text string). I guess that is why they call it a numberformat. -- Regards, Tom Ogilvy "Peter" <****************** wrote in message ... Hello everyone, Case: I enter a mobile (dutch) mobile phonenumber (06 12345678) into a cell that has no number formatting setting it looks like this 612345678. I can make ik look like this: 06 12 345 678 (this is what it reads like when checking the invoice form the phone company). Problem: I want to make a number format (in the first instance I'd like to try it without using VBA) which makes a dutch car license plate with these characters :02GDTT (example) look like 02 GD TT. Unfortunately I don't know how to do this using the regular excel dialog for numberformatting because I don't know which character represents an alphabetic character. TIA Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
Mixing number formats in one cell | Excel Discussion (Misc queries) | |||
Consitional Formatin '97 | New Users to Excel | |||
macro to bold number of chars from end of a string | Excel Discussion (Misc queries) | |||
How to Format Excel Sheet ToShowRowNumbers but not the column alph | Excel Discussion (Misc queries) |