After my recent article Using Excel to Manipulate Text, I received an email from subscriber Noel asking for help. He was trying to use Excel for some manipulation of text, having trouble with numbers converting:
Gidday from NZ (again) Terry
I liked the article "Using Excel to Manipulate Text", and wonder whether you might be able to assist me in a little exercise. I work for a training organisation, and with each new class intake I need to generate usernames and passwords. Students can then log on to any PC and manage the data they generate from a server-allocated drive
The convention we use is that the username comprises the student’s first name plus the first letter of their surname. Thus, I would become ‘noele’ and you would generate as ‘terrys’
The firstname and surname reside in separate fields in my database, so what I need to do is concatenate the first name with just the first letter of the surname. Using the ‘concatenate’ or ‘left’ functions can we do that?
Our password convention is to generate four letters and four numbers, e.g. abcd1234. I have been using Random.org to generate the alpha and numeric components separately and then concatenating them together using =CONCATENATE. The only problem is that the concatenation process suppresses leading zeros so that if the number generated was 0234, when concatenated I would get abcd234.
Not something to stop the sun rising in the east each morning, but I’m curious whether Excel’s data manipulation could fully and reliably generate usernames and passwords that fully follow our conventions for these fields?
The basic problem starts with a strangeness in Excel’s handling of numbers…
If you type 0234 into a cell, Excel will recognize it as a number, store it as a decimal number (even though there is no decimal shown), and display it in a number format as 234.
However, if you paste text that looks like a number from another source, such as a web page, into a cell, Excel’s treatment is different.
On a web page, each individual "number" is a separate text character. For example, on a web page, the number 123.45 is actually six characters, a 1, 2, 3, a period, 4, and a 5.
In an Excel cell, it’s actually a single floating-point number 123.45, unless the cell has been defined to be formatted as text.
In this case, copying 0234 into the Excel cell results in Excel treating the cell as temporarily formatted as text (actually, formatted as "general." If you edit the value, perhaps changing it to 0235, Excel will convert to number format, with a result of 235.
Noel’s problem is that Excel is also doing the conversion to a number when he tries to concatenate the values.
The fix is to force Excel to use a text format without manually editing the numbers that have leading zeroes. The TEXT function Fortunately, Excel offers the ability to define a number format that includes leading zeroes.
I had used other formats in the TEXT function, but not the zero-fill one, so this was an opportunity for me to experiment and learn. After reading Excel’s Help for that function, I was able to get it to do what we wanted, so I wrote back to Noel to tell him that it was possible.
I also sent him this proof so he could see it in action:
In the first example, we’re using the & function to concatenate the first name and the first letter of the last name Left(D7,1), regardless of the length of the first name.
In the second example, we end up with the same result as the first example only the first name (Noel) is less than seven characters. If it was more (e.g., Christopher), the second example would have taken the first 7 characters, while the first example would have used the whole name.
Notice that I didn’t do anything about the initial upper case letter of the name — the LOWER function would convert the entire string to lower case.
Now, to address the password and its numeric portion. By using the TEXT formula, we can convert the value in the selected cell to text, so we can combine it with the letters.
When you use the TEXT function, you have to provide formatting instructions to Excel as part of the input to the formula. You can see those options by looking at the Help (for the function.
In this case, #0000 tells Excel that the input value is a number and needs to be formatted as four characters, with any blanks filled with zeroes.
Noel wrote back immediately with an enthusiastic response:
Woo Hoo! Thanks a lot Terry!