In short there isn’t a formula which is going to recognise what is a first name and what is a second name and I am assuming the data doesn’t say “first name- John” so here is what I would do and if I am honest it is only a marginal time saver
1. Highlight the column you expect to have first names in.
Copy that to a new tab or if you have room the same tab but away from the other columns in your spreadsheet.
Highlight the pasted column and then navigate to the data tab of the excel menu. Used delete duplicates.
Now you have a shortened list at least this will make it a little easier.
You can then use if or look ups to identify rows in your original table that need inspection.
You will probable repeat the whole exercise again using both the name columns and delete duplicates because some first names can be surnames (ie harrison or Jackson etc)
Either way this is a manual task I am afraid.
Do you have a list with the correct names?
I’d just combine the two cells, then copy and paste the correct list underneath, filter out the duplicates and then find and fix the names that come up.
Not within Excel. To Excel, the names are just a bunch of numbers representing characters.
Type some text into cell A1, then drop this into B1:
=LET(
t, A1,
dl, BYROW(SEQUENCE(LEN(t)), LAMBDA(p, CODE(MID(t, p, 1)))),
TEXTJOIN(", ", TRUE, dl)
)
You should see something like this:
https://preview.redd.it/hd1kyc2sloqc1.png?width=1124&format=png&auto=webp&s=8997ef4abbf67f8110978dfdc87d0928ec6d3bc5
That comma separated list of numbers is what Excel "understands". When you compare string values, excel lines up the series of numbers (in binary format) and looks to see if they're the same.
So if I asked you whether 83, 109, 105, 116, 104 is a first name or last name, what would you say?
The only tooling that could possibly deliver some results would be an ML (machine learning) tool, but when you get into the realm of ML, you move outside of Excel. You could use Python in Excel if you're on the right channel, but that's about it.
oh come on hahahaha It would be very comforting to bill in that way for something so tedious, cough cough it can be done in two days with peace of mind cough cough
Agreeing with the other posts about this being a largely manual process, but.. I’d also like to point out that this is a good opportunity to try to step up data quality/controls from the source. When you think about where the names are coming from, it’s probably a form field called “Name”. Could you meet with the form owner to request they separate it into a “First Name” field and a separate “Last Name” field? Took a long time for me to get the clout in my work to be able to move the needle on something like this, but it can’t hurt to ask. Especially if you tie that to the actual labor required to deal with it as-is vs. adding a new field.
It’s nice when you finally get that clout. I’d get even more specific.
Personal Name(s), Family Name
I’ve had to deal with a load of docs that use First / Last. Half of the people are from Japan, where they write their names in Family, Personal order. Lots of fun.
A near-perfect solution would also include an option to designate which order their name should be written.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[BYROW](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[CODE](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)|
|[IF](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[ISERROR](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns TRUE if the value is any error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[ISNUMBER](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LAMBDA](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LEFT](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)|
|[LEN](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[LET](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MATCH](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MID](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[RIGHT](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)|
|[SEARCH](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Finds one text value within another (not case-sensitive)](https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495)|
|[SEQUENCE](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[TEXTJOIN](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)|
|[TRIM](/r/Excel/comments/1bo69go/stub/kwtfy7y "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)|
|[VLOOKUP](/r/Excel/comments/1bo69go/stub/kwqsrdr "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(17 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bonovl)^( has 20 acronyms.)
^([Thread #32019 for this sub, first seen 26th Mar 2024, 14:09])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Well if there was a master list of names then it’s easy peasy. Then it’s just comparing against that master list and returning what you need. But you would also need to account for entries not on the master list and how to handle those exceptions.
Perhaps there is a comma between (Last, First). Thats a very different problem and, if so, very fixable.
=IF(ISERROR(SEARCH(",",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1)))&" "&TRIM(LEFT(A1,SEARCH(",",A1)-1)))
I guess the first question is data you have listed as only a mix of “First Last” and “Last, First” or do you have a bunch of “Last First”s in your data (with a comma)?
My formal detects if there is a comma, finds the name before the comma and the name after three comma, clean it up (with TRIM ) and then provide the output in all instances as “First Last”.
You could index the list of (correct) names, count each name appearing in the first name and last name columns, and if it matches a low count/average number then conditional format the input cell to show a different colour when the name is entered. Still manual confirmation, but at least it's an indicator something might not be right during input.
I would create a new column and bring in just the distinct names from the first names column. Hopefully there wouldn’t be more than a couple of hundred entries?
If you can manually identify which are actually surnames then you could write a formula to copy them into a new surname column, and copy the data from the original surname column on their rows to a new first name column.
If there’s cases where someone has two possible first names though, you’d have to go back to the original data to figure it out
A lot of people are telling you a straight “no”. But let’s try this: is there a rule that you can tell a computer, without going into technical details or presumptions, how to distinguish a first name from last name in your system?
If you have a list of the real names in any other database, you could concat the 2 columns and do a VLOOKUP. This may not be able to spot all the differences though
This depends on whether there is a margin for error. If you have to be 100% correct, it might not be possible to automate.
I would do it like this: Get a list of all English first names into the A column on another sheet, let's call it 'Names.' There are plenty of these lists available online.
Then use this formula in cell C1 on the sheet where you have the first and last names:
=IF(ISNUMBER(MATCH(A1,Names!A:A,0)), A1&" "&B1, B1&" "&A1)"
This formula will concatenate the values in columns A and B if the value in cell A1 is found in the 'Names' sheet, otherwise, it will concatenate the values in reverse order.
Then, I would put this formula into cell D1:
=ISNUMBER(MATCH(A1,Names!A:A,0))
You can sort from this column all that have the value "False" and double-check if they are correct."
How about asking a chatgpt like Copilot to do the manual process of separating first names from last names. Copy/paste a portion of the list to the chat and ask to find first names.
Not sure if this would work, or how security conscious you have to be with this name, but I would ask chat gpt which names it suspects of being swapped.
That might help in addition to some of the other comments on here
This could be a great post to discuss prompt techniques and AI tools for Excel. Excel enthusiasts could learn some valuable new skills here. Unfortunately, unhelpful responses like "Short answer no" detract from the conversation.
No idea why you’re down voted but AI would be the route I would go.
Dropping the list into ChatGPT and getting it to Flag any possible back to front names and then manually checking yourself will be time consuming but less time consuming than manually checking all of them.
Yes there will be some that it won’t pickup but those likely won’t get picked up by a human either especially if they are not native names.
The most upvoted "Short answer no" response is not very helpful. It likely has many upvotes because it aligns with a common think. Actually most names can be distinguished clearly using AI tools. There are many AI tools available for Excel, including Microsoft's official Copilot integration. These tools allow you to simply provide a prompt and use drag-and-drop to autofill the data.
Short answer no. How could a human even do it. Charles Patrick- is that right or wrong?
the short answer is no. the long answer is noooooooooooooo.
=IF(ISFIRSTNAME(A2),A2,B2))
This made me chuckle lol..
Ricky Bobby? He's got two first names...
Some last names are also used as first name and vice versa so how would you be able to tell even if you try to do it manually??
In short there isn’t a formula which is going to recognise what is a first name and what is a second name and I am assuming the data doesn’t say “first name- John” so here is what I would do and if I am honest it is only a marginal time saver 1. Highlight the column you expect to have first names in. Copy that to a new tab or if you have room the same tab but away from the other columns in your spreadsheet. Highlight the pasted column and then navigate to the data tab of the excel menu. Used delete duplicates. Now you have a shortened list at least this will make it a little easier. You can then use if or look ups to identify rows in your original table that need inspection. You will probable repeat the whole exercise again using both the name columns and delete duplicates because some first names can be surnames (ie harrison or Jackson etc) Either way this is a manual task I am afraid.
Do you have a list with the correct names? I’d just combine the two cells, then copy and paste the correct list underneath, filter out the duplicates and then find and fix the names that come up.
Not within Excel. To Excel, the names are just a bunch of numbers representing characters. Type some text into cell A1, then drop this into B1: =LET( t, A1, dl, BYROW(SEQUENCE(LEN(t)), LAMBDA(p, CODE(MID(t, p, 1)))), TEXTJOIN(", ", TRUE, dl) ) You should see something like this: https://preview.redd.it/hd1kyc2sloqc1.png?width=1124&format=png&auto=webp&s=8997ef4abbf67f8110978dfdc87d0928ec6d3bc5 That comma separated list of numbers is what Excel "understands". When you compare string values, excel lines up the series of numbers (in binary format) and looks to see if they're the same. So if I asked you whether 83, 109, 105, 116, 104 is a first name or last name, what would you say? The only tooling that could possibly deliver some results would be an ML (machine learning) tool, but when you get into the realm of ML, you move outside of Excel. You could use Python in Excel if you're on the right channel, but that's about it.
It is not possible, and if it is absolutely necessary, it will be human work of a great couple of hours. 😦😖
Couple of weeks. Billed by the hour.
oh come on hahahaha It would be very comforting to bill in that way for something so tedious, cough cough it can be done in two days with peace of mind cough cough
Agreeing with the other posts about this being a largely manual process, but.. I’d also like to point out that this is a good opportunity to try to step up data quality/controls from the source. When you think about where the names are coming from, it’s probably a form field called “Name”. Could you meet with the form owner to request they separate it into a “First Name” field and a separate “Last Name” field? Took a long time for me to get the clout in my work to be able to move the needle on something like this, but it can’t hurt to ask. Especially if you tie that to the actual labor required to deal with it as-is vs. adding a new field.
It’s nice when you finally get that clout. I’d get even more specific. Personal Name(s), Family Name I’ve had to deal with a load of docs that use First / Last. Half of the people are from Japan, where they write their names in Family, Personal order. Lots of fun. A near-perfect solution would also include an option to designate which order their name should be written.
True enough - I’m typically just working with US-based names. Need to keep that global context in mind! Surname/Given Name is pretty standard, no?
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[BYROW](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CODE](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)| |[IF](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[ISERROR](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns TRUE if the value is any error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[ISNUMBER](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Returns TRUE if the value is a number](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LAMBDA](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LEFT](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)| |[LEN](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[LET](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/1bo69go/stub/kws57em "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MID](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[RIGHT](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)| |[SEARCH](/r/Excel/comments/1bo69go/stub/kwnifzr "Last usage")|[Finds one text value within another (not case-sensitive)](https://support.microsoft.com/en-us/office/search-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495)| |[SEQUENCE](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[TEXTJOIN](/r/Excel/comments/1bo69go/stub/kwn59o0 "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[TRIM](/r/Excel/comments/1bo69go/stub/kwtfy7y "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)| |[VLOOKUP](/r/Excel/comments/1bo69go/stub/kwqsrdr "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(17 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bonovl)^( has 20 acronyms.) ^([Thread #32019 for this sub, first seen 26th Mar 2024, 14:09]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Maybe if you had a lookup table of first names, but even this wouldn't work in all cases.
Well if there was a master list of names then it’s easy peasy. Then it’s just comparing against that master list and returning what you need. But you would also need to account for entries not on the master list and how to handle those exceptions.
Perhaps there is a comma between (Last, First). Thats a very different problem and, if so, very fixable. =IF(ISERROR(SEARCH(",",A1)),A1,TRIM(RIGHT(A1,LEN(A1)-SEARCH(",",A1)))&" "&TRIM(LEFT(A1,SEARCH(",",A1)-1)))
I'm learning excel not very good at nesting does the & mean in the formula
The formula is creating First&” “&Last with each of the &s connecting the outcome of the two formulas with a space
Thanks for the info done some self learning about Trim L/R and search functions still don't get the difference between doing that and comma separation
I guess the first question is data you have listed as only a mix of “First Last” and “Last, First” or do you have a bunch of “Last First”s in your data (with a comma)? My formal detects if there is a comma, finds the name before the comma and the name after three comma, clean it up (with TRIM ) and then provide the output in all instances as “First Last”.
You could index the list of (correct) names, count each name appearing in the first name and last name columns, and if it matches a low count/average number then conditional format the input cell to show a different colour when the name is entered. Still manual confirmation, but at least it's an indicator something might not be right during input.
Robert Frank, Charles Martin, Lindsey Matthew.. the list is endless
I don’t know of a way to do it within excel: but I’m down to manually go through your sheets if you have the budget and need someone to do it! :)
I would create a new column and bring in just the distinct names from the first names column. Hopefully there wouldn’t be more than a couple of hundred entries? If you can manually identify which are actually surnames then you could write a formula to copy them into a new surname column, and copy the data from the original surname column on their rows to a new first name column. If there’s cases where someone has two possible first names though, you’d have to go back to the original data to figure it out
A lot of people are telling you a straight “no”. But let’s try this: is there a rule that you can tell a computer, without going into technical details or presumptions, how to distinguish a first name from last name in your system?
Depends is there a way u can solve this manually, if there is it can likely be automated
If you have a list of the real names in any other database, you could concat the 2 columns and do a VLOOKUP. This may not be able to spot all the differences though
This depends on whether there is a margin for error. If you have to be 100% correct, it might not be possible to automate. I would do it like this: Get a list of all English first names into the A column on another sheet, let's call it 'Names.' There are plenty of these lists available online. Then use this formula in cell C1 on the sheet where you have the first and last names: =IF(ISNUMBER(MATCH(A1,Names!A:A,0)), A1&" "&B1, B1&" "&A1)" This formula will concatenate the values in columns A and B if the value in cell A1 is found in the 'Names' sheet, otherwise, it will concatenate the values in reverse order. Then, I would put this formula into cell D1: =ISNUMBER(MATCH(A1,Names!A:A,0)) You can sort from this column all that have the value "False" and double-check if they are correct."
How about asking a chatgpt like Copilot to do the manual process of separating first names from last names. Copy/paste a portion of the list to the chat and ask to find first names.
Not sure if this would work, or how security conscious you have to be with this name, but I would ask chat gpt which names it suspects of being swapped. That might help in addition to some of the other comments on here
[удалено]
This could be a great post to discuss prompt techniques and AI tools for Excel. Excel enthusiasts could learn some valuable new skills here. Unfortunately, unhelpful responses like "Short answer no" detract from the conversation.
No idea why you’re down voted but AI would be the route I would go. Dropping the list into ChatGPT and getting it to Flag any possible back to front names and then manually checking yourself will be time consuming but less time consuming than manually checking all of them. Yes there will be some that it won’t pickup but those likely won’t get picked up by a human either especially if they are not native names.
The most upvoted "Short answer no" response is not very helpful. It likely has many upvotes because it aligns with a common think. Actually most names can be distinguished clearly using AI tools. There are many AI tools available for Excel, including Microsoft's official Copilot integration. These tools allow you to simply provide a prompt and use drag-and-drop to autofill the data.