Auto Format of Data Entry

[Ark68]

I have a column of cells that the data takes on the same format...

N### ## ##

I just want to be able to quickly type in the 7 numbers (ie 1234567), and when I exit that cell it takes on the format N123 45 67.

How do I go about doing that?

 

[FishingIsMoreFun]

Sorry I have entered a wrong answer here.

cheers

 

[T. Valko]

I have a column of cells that the data takes on the same format...

N### ## ##

I just want to be able to quickly type in the 7 numbers (ie 1234567), and when I exit that cell it takes on the format N123 45 67.

How do I go about doing that?

Will your number ever have leading zeros?

 

[Ark68]

Yes .. they could have leading zeros.

I've tried this custom format ....

"N"### ## ##

but it doesn't work if the first number is 0.

 

[T. Valko]

Yes .. they could have leading zeros.

I've tried this custom format ....

"N"### ## ##

but it doesn't work if the first number is 0.

Try this...

"N"000 00 00

 

[Ark68]

Awesome!!! Exactly what was needed. Thank you!

 

[T. Valko]

Awesome!!! Exactly what was needed. Thank you!

You're welcome. Thanks for the feedback!

 

[Ark68]

Hi folks,

OK ... now, I need to take this one step further. Although now these cells are formatted properly to provide the visual needs, they are not providing the data I need to extract from them in left, right, mid formulae.

For example. By entering 0591904 in cell a2, the resulting cell shows N059 19 04

I now need to break it down ... b2=left(a2,4) with expected results "N059"; right(a2,2) with expected results "04"

But b2's result is 5919, not N059 ...

I see what it's doing ... it's taking the first 4 characters of the data entered, not the displayed value.

What do I need to do to extract from the displayed data?

 

[T. Valko]

Hi folks,

OK ... now, I need to take this one step further. Although now these cells are formatted properly to provide the visual needs, they are not providing the data I need to extract from them in left, right, mid formulae.

For example. By entering 0591904 in cell a2, the resulting cell shows N059 19 04

I now need to break it down ... b2=left(a2,4) with expected results "N059"; right(a2,2) with expected results "04"

But b2's result is 5919, not N059 ...

I see what it's doing ... it's taking the first 4 characters of the data entered, not the displayed value.

What do I need to do to extract from the displayed data?

For the left characters try one of these...

=LEFT(TEXT(A1,"""N""000 00 00"),4)

If ALL the entries are formatted to start with the letter N then this may be a bit simpler (less cryptic):

="N"&LEFT(TEXT(A1,"000 00 00"),3)

 

[Ark68]

Haha ... outstanding. Working wonderfully.
OK ... I know I said I needed to only go one more step further. But maybe I lied ... I have ONE additional function I'd like to do ....

Here is my formula:

=CONCATENATE($B292," ",$C292," ",LEFT(TEXT($G292,"""N""000 00 00"),4),".",MID(TEXT($G292,"""N""000 00 00"),6,2),".",RIGHT(TEXT($G292,"""N""000 00 00"),2),".000"," ",LEFT(TEXT($H292,"""W""000 00 00"),4),".",MID(TEXT($H292,"""W""000 00 00"),6,2),".",RIGHT(TEXT($H292,"""W""000 00 00"),2),".000","   ;",$D292,", ",$E292)

I'd like to add some colour to the result ...

ZFN 392 N064.54.24.000 W125.33.54.000 ;Tulita, NT