formula help
 

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)

formula help
 

Expanding the formula from your other post:

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

Biff

"bsatstudent5" wrote in message
...
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)

formula help
 

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.

"bsatstudent5" wrote:

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)

formula help
 

Also, I need the formula to add the four numbers I need after the last name.

"bsatstudent5" wrote:

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.

"bsatstudent5" wrote:

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)

formula help
 

A1 = <spaceSingh, 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

"bsatstudent5" wrote in message
...
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.

"bsatstudent5" wrote:

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)

formula help
 

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.

"T. Valko" wrote:

A1 = <spaceSingh, 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

"bsatstudent5" wrote in message
...
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.

"bsatstudent5" wrote:

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)

formula help
 

The outcome I am looking for is "ssingh####".
The "#" is for the four numbers that needs to follow the name.

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

A1 = <spaceSingh, S.
B1 = 1234

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

Returns: ssingh1234

Biff

"bsatstudent5" wrote in message
...
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.

"T. Valko" wrote:

A1 = <spaceSingh, 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

"bsatstudent5" wrote in message
...
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.

"bsatstudent5" wrote:

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)

formula help
 

The numbers are located in B1 but there are 9 numbers and I only need the
first four numbers.

"T. Valko" wrote:

The outcome I am looking for is "ssingh####".
The "#" is for the four numbers that needs to follow the name.

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

A1 = <spaceSingh, S.
B1 = 1234

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

Returns: ssingh1234

Biff

"bsatstudent5" wrote in message
...
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.

"T. Valko" wrote:

A1 = <spaceSingh, 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

"bsatstudent5" wrote in message
...
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.

"bsatstudent5" wrote:

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)

formula help
 

Thank you, I got it!

"T. Valko" wrote:

The outcome I am looking for is "ssingh####".
The "#" is for the four numbers that needs to follow the name.

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

A1 = <spaceSingh, S.
B1 = 1234

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

Returns: ssingh1234

Biff

"bsatstudent5" wrote in message
...
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.

"T. Valko" wrote:

A1 = <spaceSingh, 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

"bsatstudent5" wrote in message
...
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.

"bsatstudent5" wrote:

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)

formula help
 

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

Biff

"bsatstudent5" wrote in message
...
Thank you, I got it!

"T. Valko" wrote:

The outcome I am looking for is "ssingh####".
The "#" is for the four numbers that needs to follow the name.

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

A1 = <spaceSingh, S.
B1 = 1234

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

Returns: ssingh1234

Biff

"bsatstudent5" wrote in message
...
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.

"T. Valko" wrote:

A1 = <spaceSingh, 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

"bsatstudent5" wrote in
message
...
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.

"bsatstudent5" wrote:

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)