I’m working on a Google Sheets project and need help reorganizing my data. Here’s what I’ve got:
Alice | Smith
Senior Dev
TX
111-22-33
Fax: 444-55-66
x@y
Bob
Junior Dev
FL
777-88-99
z@w
Charlie | Jones
Mid-level Dev | Scrum Master
FL
000-11-22
v@u
I want to change it to look like this:
Alice Smith | Senior Dev | [blank] | TX | 111-22-33 | Fax: 444-55-66 | x@y
Bob | Junior Dev | [blank] | FL | 777-88-99 | [blank] | z@w
Charlie Jones | Mid-level Dev | Scrum Master | FL | 000-11-22 | [blank] | v@u
The names are in bold, so I can use that to combine first and last names. However, I’m stuck on how to handle the fax numbers and leave cells blank when necessary. I also want to start a new row after each email address appears.
Is it possible to perform this restructuring in Google Sheets? Any advice or formulas that could help would be greatly appreciated!
I’ve tackled similar data restructuring tasks in Google Sheets before. Here’s what worked for me:
First, use SPLIT to separate your data into columns based on line breaks. Then, employ REGEXEXTRACT to pull out specific info like names and job titles. For the fax numbers, create a separate column with an IF statement to check for ‘Fax:’ in the relevant cells.
To handle the blank cells, you can use IFERROR wrapped around your formulas. This will return an empty string if the extraction fails.
For starting new rows after each email, I found it helpful to use the FILTER function to create a separate range for each entry, then use ARRAYFORMULA to apply your restructuring logic across all entries.
It might take some tweaking, but this approach should get you close to your desired output. Let me know if you need more specific formula examples!
This is certainly achievable in Google Sheets. I’d recommend using a combination of ARRAYFORMULA, SPLIT, and REGEXEXTRACT functions to parse your data into the desired format. For handling the fax numbers and blank cells, nested IF statements can be employed. To start a new row after each email, you could utilize the QUERY function with custom separators.
One approach might be to first split your data into separate columns based on line breaks, then use REGEXEXTRACT to pull out specific pieces of information like names, job titles, and contact details. For the fax numbers, you could create a separate column that checks for the presence of ‘Fax:’ in the relevant cell.
Keep in mind that this might require some trial and error, especially for edge cases in your data. If you’re dealing with a large dataset, you may want to consider using Google Apps Script for more complex manipulations.
yeah, u can def do this in sheets! use ARRAYFORMULA with SPLIT and REGEXEXTRACT to separate data into columns. for blanks, use IF statements. QUERY can help combine rows. might need custom formulas for tricky parts like fax #s. good luck with ur project!