Splitting Firstname and Lastname in Excel with Different Cases

·

How can we split a firstname and a lastname in a cell ? Firstname is writted like this and lastname is ALL CAPs

Clay
APP
·
·
You can split names using Extract Data From Values with a custom regex pattern. For your specific case with firstname (normal case) and lastname (ALL CAPS): 1. Add a new column using Add Column 2. Use Extract Data From Values to process your name data 3. Apply a custom extraction regex that identifies the pattern between normal case and ALL CAPS text Alternatively, you can use an AI column like ChatGPT Generate Text with pre-built prompts to normalize and split the names into separate fields. This will split the values into separate components that you can then send to different columns.
Sources: Write to Other Table | Documentation | Clay University Write to Other Table | Documentation | Clay University Table columns | Documentation | Clay University
Stefano
·
·
Hi Pierre, thank you for reaching out. We can do this using formulas on Clay. However, this will depend on the formatting of the full name cell. Pierre - Do you mind sending the link (url) to the table so we can take a look?
(52 kB)
hunainfenny
·
·
Use this Excel formula:
=PROPER(LEFT(A1,FIND(" ",A1)-1)) ← First Name =UPPER(RIGHT(A1,LEN(A1)-FIND(" ",A1))) ← Last Name
Example If cell A1 has: John DOE Output: First Name: John Last Name: DOE