Cell format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mcg - 06 Jan 2006 08:54 GMT

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

Reply to this Message

Pete_UK - 06 Jan 2006 09:29 GMT

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

Reply to this Message

Ken Wright - 06 Jan 2006 14:35 GMT

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.

Signature

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

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

Reply to this Message

Dave Peterson - 06 Jan 2006 15:24 GMT

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)

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

Signature

Dave Peterson

Reply to this Message

Gazeta - 06 Jan 2006 21:05 GMT

> Maybe you can enter the data in one column and use another column to show the
> pretty format:
[quoted text clipped - 10 lines]
>
> Dave Peterson

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

Reply to this Message