formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of names as lastname, firstname. I have a list of nine numbers
that need to be connected to the name, but I only need the first four
numbers. I need a formula to make them first initial (no period) lastname
followed by the four numbers. Also, the first letter of the lastname,
firstname are in uppercase and the whole name needs to be in lowercase, and
some names have a space at the beginning and I need to "trim" that space.
Does anyone know of an Excel formula or function that will do this?

An example is " Singh, S." (the name with the space at the beginning)
An example is "Wimberly, J." (the name with no space at the beginning)
 
Expanding the formula from your other post:

=TRIM(LOWER(MID(A1,FIND(",",A1)+2,1)&" "&LEFT(A1,FIND(",",A1)-1)))

Biff
 
This didn't work because it took away the last name and I need the last name.
I need to take away the empty space before the last name. I have to use the
TRIM function.
 
A1 = <space>Singh, S.

(as per your example)

=TRIM(LOWER(MID(A1,FIND(",",A1)+2,1)&" "&LEFT(A1,FIND(",",A1)-1)))

Returns: s singh

With the previous leading space trimmed off.

Biff
 
I do not need the space between "s singh". The outcome I am looking for is
"ssingh####". The "#" is for the four numbers that needs to follow the name.
 
The outcome I am looking for is "ssingh####".
The "#" is for the four numbers that needs to follow the name.
Click to expand...

Where are the numbers located? Assume they're in B1:

A1 = <space>Singh, S.
B1 = 1234

=LOWER(MID(A1,FIND(",",A1)+2,1)&TRIM(LEFT(A1,FIND(",",A1)-1)))&B1

Returns: ssingh1234

Biff
 
The numbers are located in B1 but there are 9 numbers and I only need the
first four numbers.
 
Thank you, I got it!

T. Valko said:
Where are the numbers located? Assume they're in B1:

A1 = <space>Singh, S.
B1 = 1234

=LOWER(MID(A1,FIND(",",A1)+2,1)&TRIM(LEFT(A1,FIND(",",A1)-1)))&B1

Returns: ssingh1234

Biff
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting a message box program to run (Page 43 of 'BeginningProgramming with VBA' by Duane Birnham) 11
Pesky spaces 1
access 2
Is there a way to separate FirstName and LastName ? 4
Can I use concatenate with Index(match) function? 3
Combination of text in 2 columns 4
How to compare and extract data in two columns 1
Separate a FirstLast text string into two columns 5

Back
Top