number formatin XL 2000: mixing alph.chars.& numbers

[Peter]

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

 

[Niek Otten]

Hi Peter,

=LEFT(A1,2)&" "&MID(A1,3,2)&" "&RIGHT(A1,2)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ron Rosenfeld]

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

 

[Tom Ogilvy]

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.