automating in excel 2003

[toyden]

Hi guy’s I’m looking for a way to automate a cell entry in excel 2003.
The user types in a set of 25 characters and there needs to be a “-“ between every 5 characters I created a custom cell format #####”-“#####”-“#####”-“#####”-“#####
But this dose not but the dash in for me, Can anyone tell me if I can do what I’m trying to do and if so how.

Any help is appreciated
Regards,
Toyden

 

[SkipVought]

Hi,

Formatting is one of those Excel feature that is often understood.

Have you just tried to enter a 25-digit number? It does not work. You are limited to around 15 places, due to the storage limits of numeric values in Excel.

Formatting only works on numeric values, but not on numeric text values -- BIG difference!

Bottom line: if you want your numeric text with dash every 5 characters, then that's EXACTLY how you must enter the text. Oh, yes, and FORMAT the cells as text BEFORE you begin.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[toyden]

thanks Skip

ill just keep moving on and enter them manualy
regards,
Toyden

 

[Kurupt55]

It would be a little bit messy and would take up another colum but could you not use a CONCATENATE formula

 

[Kurupt55]

=CONCATENATE(LEFT(A1,5),"-",RIGHT(LEFT(A1,10),5),"-",RIGHT(LEFT(A1,15),5),"-",RIGHT(LEFT(A1,20),5),"-",RIGHT(A1,5))

 

[toyden]

thanks ill give it ago

 

[SkipVought]

Using Left AND Right is rather tedious...
[tt]
=CONCATENATE(LEFT(A1,5),"-",MID(A1,10,5),"-",MID(A1,15,5),"-",MID(A1,20,5),"-",RIGHT(A1,5))
[/tt]
If you use the Data > List - Create List feature, Excel will propogate your formula to the new row when data is entered.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[toyden]

keep the ideas coming guys
cheers