Cell format

[mcg]

hi,
how can i format cell containing WI12345 to be displayd asWI 12345 (
with space after 2 signs )
thanks
mcg

 

[Pete_UK]

Assuming your data is in cell A1, and that you allways want 2
characters before the space, the following formula will do this:

=LEFT(A1,2) &" "& RIGHT(A1,LEN(A1) - 2)

Hope this helps.

Pete

 

[Ken Wright]

Don't believe you can, because you have text in the cell and not a number.
Are all the prefixes WI because if so then you can get rid of all the WIs
and then format the remaining numbers to appear just the way wanted,
including the WI prefix.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Dave Peterson]

Maybe you can enter the data in one column and use another column to show the
pretty format:

=left(a1,2)&" "&mid(a2,3,255)

 

[Gazeta]

Maybe you can enter the data in one column and use another column to show the
pretty format:

=left(a1,2)&" "&mid(a2,3,255)

thanks for suggestions
i will have to force user to enter it as wi12345 which is registration plate
for car
or i will have to vlookup for =left(a1,2)&" "&mid(a2,3,255)...
mcg