How do I format and truncate labels in MS Excel?

Question

I have a list of names formatted:
Last Name, First Name, MI.

in MS Excel. Is it possible for Excel to format them into an eight letter string with the first six of the last name, then first initial, then middle initial.

i.e.

Answers, Yahoo G. becomes answeryg

if a last name is less than six characters, the first name is used to fill the space

Smith, Ryan B. becomes smithryb

I have a list of names formatted:
Last Name, First Name, MI.

in MS Excel. Is it possible for Excel to format them into an eight letter string with the first six of the last name, then first initial, then middle initial.

i.e.

Answers, Yahoo G. becomes answeryg

if a last name is less than six characters, the first name is used to fill the space

Smith, Ryan B. becomes smithryb

Also, I should have mentioned, these are all in one cell

Answer 1

Try this formula:

=MID(A1,1,6) & MID(B1,1,2) & MID(C1,1,1)

That covers the first part easily by taking the Cell (A1) start from the first character (,1) and going 6 characters into the string (,6).
If middle is always just an initial instead of middle name you can just use C1 instead of MID(C1,1,1)

The second part with a short last name is trickier..it involves trimming and checking the length of the first string in a conditional statement like:

If LEN(MID(A1,1,6).TRIM) < 6 then
X = 6 - LEN(MID(A1,1,6).TRIM) + 2
End If

In your example, SMITH, the length of the string after trimming the space off the end is 5.
6-5 = 1 + 2 = 3 so X = 3

Put the X in for length of the first name string so you use 3 characters instead of 2 like:

=MID(A1,1,6) & MID(B1,1,X) & MID(C1,1,1)

The conditional will have to go in VBA code behind the scenes instead of in the cell like the original formula.

Hope that helps

Answer 2

The constant factor is the space before the initials. You can use the find() string to locate the first space character and the second one. The letter after the spaces are the initials. You now got that. Whatever in front of the first space is the first word. Truncate that to eight character if neccessary. Join them.

Answer 3

Assuming you have Last, First, Middle in cells A1,A2,and A3:

= (LEFT (A1,6) & LEFT (B1,1) & LEFT (C1,1) )

Get rid of the spaces - had to add so Yahoo would show the whole thing.

Breaks down to:

Starting from LEFT side grab first 6 characters
Starting from LEFT side grab first 1 character
Starting from LEFT side grab first 1 character

Answer 4

Yes it is possible

Use the CONCATENATE, and LEFT functions and write conditional statements to handle the issue of less than 8 characters in last name.

Answer 5

i'm a database administrator (excel nerd). there is no thanks to insert both textual content and a function calculation (equation) right into a cellular. you are able to in spite of the undeniable fact that insert it right into a cellular next to the single containing textual content. you are able to format it in the variety of fashion that it would want to look as if one cellular. I.E. - same colour, get rid of border, justify the textual content, etc. wish this permits!