| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Hybrid Mode
