Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Separate name per column in Excel

Status
Not open for further replies.

TheLazyPig

Programmer
Sep 26, 2019
95
PH
Hi!

I have a set of names in a column. Is it possible to separate it from different columns using excel?

Untitled_dzblj3.png


Thanks!
 
You can use power query, formulas, or text to columns functionality to extract words to separate cells. In your case you additionally need to implement some logic to keep some words together (as last entry),or have empty cells in the middle (as first entry). Due to combining some words in a single text I'd try to use power query, however in this case excel is used for input and output only, the power query inside uses M language.

combo
 
If this is a one-time thing, I would seriously just use the text to columns functionality that combo mentioned here.

Here is one walkthrough if you need it:

I have zero affiliation with that site. I just simply searched online for instructions rather than typing them up myself. Here's my search if you want (didn't use Google, and tend to not use Google much anymore):


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
 
That's gruesome. De La Paz Roel Vergel Villarazo... how would you know where the first name ends, the middle name starts, etc?

The first thing that you'd have to do to make this work is have clearly definable rules for what you're trying to do. For the last column, you could use some sort of "If" or "Case" statement for Jr Sr II III IV etc... but splitting on some of those first, middle and last names are going to be really difficult. I mean, even with the first row, why is "John" not the middle name?



Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg
 
TLP,

When you post a problem like this that is not a simple problem and needs actual data that can be manipulated in a Microsoft Office application, presumably Excel, if you really want people to take the time to develop a workable solution then post data that can actually be COPIED n PASTED into an Excel spreadsheet.

A picture, here is K*worthless.

I've actually done some stuff similar and although you may develop rules and tables (like I'd put Jr Sr II III IV etc in a table, not in my code) I most often ended up doing a series of passes, winnowing out the successes making code changes and repeat. Most were one-off tasks. Hardly any were made production-ready. Probably none. All VERY time consuming.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
 
Hi!

Thank you for all the replies. I really had a hard time on this, I already have a solution and it doesn't need to be separate anymore because the database has a full name column. Before I found out that it has a full name column, I tried to separate it by using text-to-column but it didn't work out as I wanted.

:)

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top