I have an Excel sheet with first names in Column A and surnames in Column B. I want to create a third Column C that contains the first character from the first name and adds it to the surname, creating first initial + surname.
我有一个Excel表格,列有A和b列的第一个名字,我想创建一个第三列C,它包含了第一个名字的第一个字符,并将它添加到姓氏中,创建了第一个初始+姓氏。
First Name Last Name Combined Name
John Smith jsmith
How can I do this using Excel?
我如何使用Excel做这件事?
6 个解决方案
#1
64
=CONCATENATE(LEFT(A1,1), B1)
Assuming A1 holds 1st names; B1 Last names
假设A1为名;B1姓氏
#2
20
Personally I like the & function for this
我个人喜欢这个&函数
Assuming that you are using cells A1 and A2 for John Smith
假设你用A1和A2表示约翰·史密斯
=left(a1,1) & b1
If you want to add text between, for example a period
如果你想在中间添加文本,例如句号
=left(a1,1) & "." & b1
#3
3
Use following formula:
使用以下公式:
=CONCATENATE(LOWER(MID(A1,1,1)),LOWER( B1))
for
为
Josh Smith = jsmith
note that A1 contains name and B1 contains surname
注意A1包含名字,B1包含姓氏
#4
1
This is what formula I used in order to get the first letter of the first name and first letter of the last name from 2 different cells into one:
这就是我用来从两个不同的单元格中得到名字的第一个字母和名字的第一个字母的公式:
=CONCATENATE(LEFT(F10,1),LEFT(G10,1))
Lee Ackerman = LA
#5
1
Not sure why no one is using semicolons. This is how it works for me:
不知道为什么没有人使用分号。这就是我的工作方式:
=CONCATENATE(LEFT(A1;1); B1)
Solutions with comma produce an error in Excel.
使用逗号的解决方案会在Excel中产生错误。
#6
-1
QUESTION was: suppose T john is to be converted john T, how to change in excel?
问题是:假设T john要转变成T,如何在excel中改变?
If text "T john" is in cell A1
如果文本“T john”在A1单元格中
=CONCATENATE(RIGHT(A1,LEN(A1)-2)," ",LEFT(A1,1))
and with a nod to the & crowd
向人群点头致意。
=RIGHT(A1,LEN(A1)-2)&" "&LEFT(A1,1)
takes the right part of the string excluding the first 2 characters, adds a space, adds the first character.
取字符串的右部分,不包括前两个字符,添加空格,添加第一个字符。
#1
64
=CONCATENATE(LEFT(A1,1), B1)
Assuming A1 holds 1st names; B1 Last names
假设A1为名;B1姓氏
#2
20
Personally I like the & function for this
我个人喜欢这个&函数
Assuming that you are using cells A1 and A2 for John Smith
假设你用A1和A2表示约翰·史密斯
=left(a1,1) & b1
If you want to add text between, for example a period
如果你想在中间添加文本,例如句号
=left(a1,1) & "." & b1
#3
3
Use following formula:
使用以下公式:
=CONCATENATE(LOWER(MID(A1,1,1)),LOWER( B1))
for
为
Josh Smith = jsmith
note that A1 contains name and B1 contains surname
注意A1包含名字,B1包含姓氏
#4
1
This is what formula I used in order to get the first letter of the first name and first letter of the last name from 2 different cells into one:
这就是我用来从两个不同的单元格中得到名字的第一个字母和名字的第一个字母的公式:
=CONCATENATE(LEFT(F10,1),LEFT(G10,1))
Lee Ackerman = LA
#5
1
Not sure why no one is using semicolons. This is how it works for me:
不知道为什么没有人使用分号。这就是我的工作方式:
=CONCATENATE(LEFT(A1;1); B1)
Solutions with comma produce an error in Excel.
使用逗号的解决方案会在Excel中产生错误。
#6
-1
QUESTION was: suppose T john is to be converted john T, how to change in excel?
问题是:假设T john要转变成T,如何在excel中改变?
If text "T john" is in cell A1
如果文本“T john”在A1单元格中
=CONCATENATE(RIGHT(A1,LEN(A1)-2)," ",LEFT(A1,1))
and with a nod to the & crowd
向人群点头致意。
=RIGHT(A1,LEN(A1)-2)&" "&LEFT(A1,1)
takes the right part of the string excluding the first 2 characters, adds a space, adds the first character.
取字符串的右部分,不包括前两个字符,添加空格,添加第一个字符。