User-Defined Cell Format: Letters and Numbers

Discuss the spreadsheet application

User-Defined Cell Format: Letters and Numbers

Postby BiscuitEater » Sat Jan 12, 2019 7:55 pm

I want to create a User-Defined Cell Format (Format Cells > Numbers) that will allow a mixture of letters and numbers to display with automatically embedded hyphens.
For example: I want to be able to enter the following "841501F001234" and have it display as "8415-01-F00-1234".

I have tried the custom cell format of "####-##-###-####" and it works great with data of numbers only (such as "8415010001234'), but when the data contains a letter, it fails.
Is there a character other than "#" that I can use in the custom format string that will accept both numbers and letters?

Format Cells - Numbers.jpg
Format Setting
Lawrence-65
LibreOffice V.6 (x64) [or latest stable version]
Window 10
BiscuitEater
 
Posts: 2
Joined: Sat Jan 12, 2019 6:41 pm

Re: User-Defined Cell Format: Letters and Numbers

Postby Zizi64 » Sat Jan 12, 2019 8:31 pm

Format Cells > Numbers

That function works with the numbers only.

The strings are strings. The numeric characters have not numeric value in a string. And the separator characters are same parts of a strings as all of the alphanumeric characters are.

If you want not type-in the separator characters, then you can:
- type-in the parts
8415
01
F00
1234
into adjacent columns and then concatenate the parts and the separators by the CONCATENATE() function or by the & operand into helper cells in a helper column.
Code: Select all   Expand viewCollapse view
=CONCATENATE(A1;"-";B1;"-";C1;"-";D1)
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8149
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: User-Defined Cell Format: Letters and Numbers

Postby BiscuitEater » Sat Jan 12, 2019 10:59 pm

Yes, you are correct that the cell data does not have any numeric value. They are stock identification numbers which are usually composed of all numerals, but on occasion will contain one or more letters.
Using separate columns and concatenation would create other problems further on in the process, so would not ease data entry or usage.
However, thank you for your suggestion!
Lawrence-65
LibreOffice V.6 (x64) [or latest stable version]
Window 10
BiscuitEater
 
Posts: 2
Joined: Sat Jan 12, 2019 6:41 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 8 guests