who is good writing basic Excel scripts and stuff?

[AAP]

Here is what I have.

Spreadsheet with about 5000 records.

7 columns. They are :
FirstName Lastname Addresss City State Zip Hospital

Ok, here is the problem.

Some of the Firstnames are in a format like :

John

others are like :

Mr John

or

Mr. John
(with the . )

Females will have names like :

Sharon
Mrs Sharon
Mrs. Sharon
Ms Sharon
Ms. Sharon
Miss Sharon


What I want is to put a blank column on the sheet and for all records that have a salutation in the first name, for the script to extract that salutation and place it in the blank cell. If a record does not have a predetermined saluation (Mr Mr. Mrs Mrs. Ms Ms. etc..) then it is skipped.

Will pay K or $25 by paypal.

 

[puddlemonkey]

bump?

 

[mwm5]

i'm working on it bro. you are either gonna have to use an 'IF' statement or 'LEFT' function. trying to get one of them to work competely

 

[mwm5]

btw, this shit isn't any basic script. ass!

 

[puddlemonkey]

I'd like to think my bump made this all possible.

 

[mwm5]

I'd like to think my bump made this all possible.

it at least helped some one attempt. i dunno if i can get exactly what he wants. i can get it to move the prefix over but then i can't get it to delete from original box. so it looks like


mrs. mrs. shanon

 

[SpyWizard]

import into access and go from there.. fcuk excel

 

[AAP]

it at least helped some one attempt. i dunno if i can get exactly what he wants. i can get it to move the prefix over but then i can't get it to delete from original box. so it looks like


mrs. mrs. shanon

Actually if you could get it into it's own blank cell, I can then go highlight the Firstname Column and do a FIND/REPLACE and remove all the salutations (MR MRS MS, etc..)

 

[mwm5]

Actually if you could get it into it's own blank cell, I can then go highlight the Firstname Column and do a FIND/REPLACE and remove all the salutations (MR MRS MS, etc..)

go and use the left function. first sort them from A - Z, then go to the blank colum before and put a 'left' function with how many spaces you want to pull to the left. it should be 4 spaces on all but mr and ms (the ones with no period) those will be three. since you have them sorted you can just pull down the 'left function'. make since?

 

[puddlemonkey]

I deserve a finders fee

 

[Razorguns]

Use vbscript, iterate through the columns, extract field, compare, write to the other column. Might find some similar code online to tweak the .vbs.

r

 

[chewyxrage]

highlight entire column, goto data - text to columns, hit next - check the space box, hit finish. This will separate everything into their own column. From there you can add the Mr., Mrs., whatever in the blank cells. After you have the appropriate prefix on each name, do a concatenate command.

Ex:

A B
Mr. Dave

Are two columns. =concatenate(A1," ",B1) - resulting column will read Mr. Dave - all in one cell.

That's a roundabout way to do it, I'm not spending the time to figure out entire if statement.....

 

[chewyxrage]

damn message board screwed up my formatting, in the above post, think of Mr. being directly under column A, Dave being directly under column B, etc.

 

[puddlemonkey]

damn message board screwed up my formatting, in the above post, think of Mr. being directly under column A, Dave being directly under column B, etc.

pwned!

 

[chewyxrage]

pwned!

wtf am I even doing on this thread, I'm supposed to be HOME from work.....

 

[puddlemonkey]

wtf am I even doing on this thread, I'm supposed to be HOME from work.....

pwned again!

 

[EnderJE]

VBScript is the best. Btw, there are no tits in this thread.

 

[_Yossarian_]

you get this done bro?
if not pm me ill dit for you(free)

 

[pin]

 

[AAP]

you get this done bro?
if not pm me ill dit for you(free)

No bro, still working on it.


I have no knowledge of VBscript.

You don't mind?

 

[mwm5]

No bro, still working on it.


I have no knowledge of VBscript.

You don't mind?

i couldn't get it to work without making it more work than just retyping names.

it takes A LOT of steps to do it.

 

[AAP]

go and use the left function. first sort them from A - Z, then go to the blank colum before and put a 'left' function with how many spaces you want to pull to the left. it should be 4 spaces on all but mr and ms (the ones with no period) those will be three. since you have them sorted you can just pull down the 'left function'. make since?

No really.

Another problem is that sometimes in the Firstname field people write both their first and middle names, so that eliminates successfully using the Text to Column function is you establish by SPACE because you end up having to go and concecate the columns later.

 

[Razorguns]

You need to go to Indian Elite Fitness.com. No one knows IT stuff anymore in America.

r

 

[Dial_tone]

You should be able to use the text to columns feature and do that in seconds.

 

[Smurfy]

Here is what I have.

Spreadsheet with about 5000 records.

7 columns. They are :
FirstName Lastname Addresss City State Zip Hospital

Ok, here is the problem.

Some of the Firstnames are in a format like :

John

others are like :

Mr John

or

Mr. John
(with the . )

Females will have names like :

Sharon
Mrs Sharon
Mrs. Sharon
Ms Sharon
Ms. Sharon
Miss Sharon


What I want is to put a blank column on the sheet and for all records that have a salutation in the first name, for the script to extract that salutation and place it in the blank cell. If a record does not have a predetermined saluation (Mr Mr. Mrs Mrs. Ms Ms. etc..) then it is skipped.

Will pay K or $25 by paypal.

dont be lazy. just go through all 5000 lines and do it manually.

 

[AAP]

You should be able to use the text to columns feature and do that in seconds.

no good. because then I end up with random names in extra columns for the shit head that include their middle names in the FIRSTNAME column.

 

[Razorguns]

Ya lazy bum! 5 seconds in google.

Excel - Split names into several Excel cells - Name Splitter

Americans.

r

 

[_Yossarian_]

No bro, still working on it.


I have no knowledge of VBscript.

You don't mind?

no worries, hit me up with file and exactly what u want/dont want

[email protected]
no need for scripts...

 

[AAP]

no worries, hit me up with file and exactly what u want/dont want

[email protected]
no need for scripts...

dude that better not be a prank email addy

 

[PICK3]

no good. because then I end up with random names in extra columns for the shit head that include their middle names in the FIRSTNAME column.

Save the file as a text file then replace spaces with commas, then save as a csv ... disgard the middle name/initial

 

[_Yossarian_]

dude that better not be a prank email addy

it's real.

 

[chewyxrage]

it's real.

do you actually use that as a normal email or just create it for this?

 

[SpyWizard]

didn't i say this already??

put the data into a access data base (use an import tool)

then you can manipulate and query it all day long..

 

[_Yossarian_]

do you actually use that as a normal email or just create it for this?

just for this, ive been out of the game for a while and all my secure addy's expired. the only emails i have left are my actual name.

PS
LOL at access!?!?!? wtf

 

[AAP]

Access is a no-go.

 

[AAP]

no worries, hit me up with file and exactly what u want/dont want

[email protected]
no need for scripts...

I am having an assistant email you the before and after examples now.