Question description:
This user has given permission to use the problem statement for this
blog.
How can I change the first letter in a string of data like shown below - so that it is lower case in each string. The data is already in excel- each example shown below would need a lower case B (b) - and are presented in the same column, different rows. Nothing else should changed just B to b - I need to do this on many sheets, so I need a formula. The data is different on each sheet, but the same issue. Thank you.
BJHS3134
BJHS3170
BJHS2819
BJHS3295
BJHS3822
BJHS3258
BJHS3365
BJHS3417
BJHS3226
BJHS4315
Solved by F. F. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/09/2018 - 06:40
Hi, Welcome to Excel Chat.
Excelchat Expert
14/09/2018 - 06:41
You want to know the formula to convert first letter to small letter and rest as it is?
User
14/09/2018 - 06:41
YEs but it needs to allow me to do it on an entire column with many rows.
Excelchat Expert
14/09/2018 - 06:41
Ok. Thanks for confirming. I am showing an example in the preview.
Excelchat Expert
14/09/2018 - 06:42
Please review in the preview.
User
14/09/2018 - 06:42
i see it but it's just one row
User
14/09/2018 - 06:42
not the same number in each row
User
14/09/2018 - 06:42
i don't see any formula displaying
Excelchat Expert
14/09/2018 - 06:42
I am working on it.
User
14/09/2018 - 06:42
ok thought you wanted me to comment on the preview
Excelchat Expert
14/09/2018 - 06:43
Ok. Please copy the info in column A meanwhile. I am working on the formula in B1 cell.
User
14/09/2018 - 06:43
I'm not sure what you mean.
User
14/09/2018 - 06:43
On my worksheets the data is already in a column.
Excelchat Expert
14/09/2018 - 06:44
Please copy paste the data in the preview to check. Copy the column containing the codes.
User
14/09/2018 - 06:44
Copy from where to where, I'm sorry I don't understand what you are asking
Excelchat Expert
14/09/2018 - 06:45
Ok No Problem. I have written formula in B1 cell.
Excelchat Expert
14/09/2018 - 06:46
It converts first letter to small letter and keep the remaining same.
User
14/09/2018 - 06:46
Can you please explain the formula since I need to apply it.
Excelchat Expert
14/09/2018 - 06:46
Please apply the correct reference in the formula.
Excelchat Expert
14/09/2018 - 06:46
A1 is the cell that is considered as input.
Excelchat Expert
14/09/2018 - 06:47
=LOWER(left(A1,1))&mid(A1,2,len(A1))
Excelchat Expert
14/09/2018 - 06:47
So, please apply relevant reference in the formula based on your sheet.
User
14/09/2018 - 06:47
What is mid and the other data referring to in the formula
Excelchat Expert
14/09/2018 - 06:47
Once a cell is completed. Copy the cell and drag downwards.
Excelchat Expert
14/09/2018 - 06:48
Mid is used for string manipulation.
Excelchat Expert
14/09/2018 - 06:48
I am extracting the string from position 2 to till the last character.
Excelchat Expert
14/09/2018 - 06:48
Lower is used to convert to small case.
Excelchat Expert
14/09/2018 - 06:48
Left is used to get the first character of the string.
User
14/09/2018 - 06:49
What is the rest referring to please
User
14/09/2018 - 06:49
Am I able to save this session with the notes and screens?
Excelchat Expert
14/09/2018 - 06:49
=mId(string,first position, length of the string)
Excelchat Expert
14/09/2018 - 06:50
Left(String, number of characters)
Excelchat Expert
14/09/2018 - 06:50
Lower(string): Converts to lower case of the string inside.
Excelchat Expert
14/09/2018 - 06:50
Left(A1,1): Extracts first character from A1 string.
Excelchat Expert
14/09/2018 - 06:51
LOWER(left(A1,1)):After extracting first character, it converts to lower case.
Excelchat Expert
14/09/2018 - 06:51
"&" is used to join the string.
User
14/09/2018 - 06:51
am i able to save this chat
Excelchat Expert
14/09/2018 - 06:51
So, i am splitting the string first and then joining it.
Excelchat Expert
14/09/2018 - 06:51
It will be saved for your future reference.
Excelchat Expert
14/09/2018 - 06:52
You can visit anytime and refer the explanation.
User
14/09/2018 - 06:53
it doesn't seem to be working when I apply it.
Excelchat Expert
14/09/2018 - 06:53
What is the reference cell? Is it A1?
Excelchat Expert
14/09/2018 - 06:54
Please change the reference cell in the formula based on your cell position.
User
14/09/2018 - 06:55
seems to be ok now thank you
Excelchat Expert
14/09/2018 - 06:55
Great to hear.
Excelchat Expert
14/09/2018 - 06:55
Please visit Got It Pro for new queries. Have a great day ahead.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
