Microsoft Excel wizards....I need help with a function

[bonafide hustla]

aight, so I found this function before using google, but I can't seem to find it so maybe one of yall can help

edit:

I have an excel list of about 100 names in the format:

firstname.lastname

ie:

michael.jordan
michael.jackson


I want to reformat them to a new format:

firstinitial.lastname

ie:

mjordan
mjackson



any help?

 

[craftsy21]

for 100 names?

just freaking get to work with the delete key.. the time you'd spend trying to figure out a formula could be spent to adjust them all.


not like you've got 10,000 man... just get it done.

 

[eaalto]

Unless you have set up an entire list that serves as an index for the list you wish to display, I don't see how that is otherwise possible. I feel like youwould have to manually type in the entire list of names, then type in the entire list of first initial, last name, and then you could apply the formula to agiven column, such that when you type michaeljordan, michaeljordan = mjordan, and it just automatically displays that information.

It would be easier just to delete the letters you don't want...

 

[bonafide hustla]

Originally Posted by Craftsy21

for 100 names?

just freaking get to work with the delete key.. the time you'd spend trying to figure out a formula could be spent to adjust them all.


not like you've got 10,000 man... just get it done.

yea but short of explaining exactly what I am doing it's an ongoing thing, so having to do this every time would be super inefficient. hopefully after i figure it out it'll be something i never have to worry about slowing me down again namean. and its really more than 100. round 200. but again, the number is kinda irrelevant here. i just threw out an arbitrary number that was somewhat high to depict a larger scale thing that needs to beautomated

 

[eaalto]

So here is another example of what you might need.
http://www.automateexcel.com/2008/08/23/excel-string-functions/

I feel like it should be easier than that, but I'm not certain. I usually go to my Dad with my excel problems, he is insane with it.

 

[throwedindagame]

You could use a Macro.......

See I'm good with algorithms, actual coding and syntax not so much.

Here's the algorithm.Hopefully someone on NT who programs Macros daily in excel can do the rest. If you don't understand the text in blue, don'tworry....someone on NT does.

Algorithm:

Your list = 100 strings (words).
Set up a function to delete every letter after the first letter and the function should end it's deleting after the delimeter.

This can be accomplished with a onditional statements and a loop. Not sure which ones are applicable in excel but I'm sure the universal "Ifstatement" and and 2 "For loops (nested)" will work.

Concept:
[For loop that advances it's position by one while the count<total number of characters in the string (word)]
If character = "."
Delete period and Kill function
Else, [For loop that deletes the character and then advances it's position in the string by one]
End Function

Also take into account that as you are deleting characters, the computer may simply leave a blank spaceholder in palce of acharacter.
Example: "Niktalk" after one cycle becomes "N ketalk"
In which case, you probably need to make a function that that juxtaposes characters while simultaneously deleting the next character.

This is why I chose engineering and not programming.

The funny thing is, even if you knew what you were doing by the time you programmed that......you might as well have done it manually. Now if you had 10,000names like another NTer mentioned. This would be good. Hell by the time I thought of a solution for you and typed the post out i could have ran through 100names.

Realtalk, I hope I gave you a starting point though. Should be good for large list....but 100 not very economical.

 

[eaalto]

Originally Posted by ThrowedInDaGame


You could use a Macro.......

See I'm good with algorithms, actual coding and syntax not so much.

Here's the algorithm.Hopefully someone on NT who programs Macros daily in excel can do the rest. If you don't understand the text in blue, don't worry....someone on NT does.

Algorithm:

Your list = 100 strings (words).
Set up a function to delete every letter after the first letter and the function should end it's deleting after the delimeter.

This can be accomplished with a onditional statements and a loop. Not sure which ones are applicable in excel but I'm sure the universal "If statement" and and 2 "For loops (nested)" will work.

Concept:
[For loop that advances it's position by one while the count<total number of characters in the string (word)]
If character = "."
Delete period and Kill function
Else, [For loop that deletes the character and then advances it's position in the string by one]
End Function

Also take into account that as you are deleting characters, the computer may simply leave a blank spaceholder in palce of a character.
Example: "Niktalk" after one cycle becomes "N ketalk"
In which case, you probably need to make a function that that juxtaposes characters while simultaneously deleting the next character.

This is why I chose engineering and not programming.

The funny thing is, even if you knew what you were doing by the time you programmed that......you might as well have done it manually. Now if you had 10,000 names like another NTer mentioned. This would be good. Hell by the time I thought of a solution for you and typed the post out i could have ran through 100 names.

Realtalk, I hope I gave you a starting point though. Should be good for large list....but 100 not very economical.

Interesting. I had no idea such functions existed. The most advanced formula I used looks like this:
=INDEX('MASTER CASHIER LIST'!$C$1:$C$133,MATCH(A21,'MASTER CASHIER LIST'!$A$1:$A$129,0))

I was in charge of writing out break boards and drop logs, and at one point we had almost 100 cashiers. Each cashier has a unique cashier number. All of theinformation that was entered on the break board was automatically referenced by the drop log, but you had to manually enter each cashier number, which meanteither memorizing them all or constantly referencing them on a list.

Column A was the Cashier's Name, Column C was the Cashier's Numbers, and the formula made it such that when I typed in a cashier's name, forexample, Erik, the cashier number, 187, would automatically show up in the next column.

That's the only way I had thought of solving OP's problem, but with a formula that deletes all but the first letter, that might be a lot more to thepoint.

 

[dirtylicious]

There's another way without creating macros...but it's multi-step...

1. open notepad
2. copy and paste data set (firstname.lastname)
3. save file
4. open excel
5. use open file in excel and browse to find the saved notepad file(you'll have to adjust the window to search for all files)
6. select tab delimited
7. use custom delimited and use "."

now you have separated the firstname and lastname into 2 separate columns

8. now just sort alphabetically by the firstname column

you'll be able to replace the first name with the first initial much easier now.

9. concatenate the two columns into a third column in the desired format

 

[dirtylicious]

so just tried it myself.
it works