A
adam cook
Hi Guys
sorry if im in the wrong section when asking this, if i am please let me know and i will re post.
I have a spreadsheet with the following columns:
Code
Name - in uppercase
Name - all lower case
Title
Status
CRI Status
The code column is generated by the 4 first characters of the last name and any initials of the previous names.
The Name in upper case is generated from the name in lower case. The name columns have the name in Full such as Mr A J Cook.
I do several other things with the sheet as well which i can do easily but im stuck when it comes to creating the Code field.
I used 'Text to Columns' feature to split the full name into 3 columns. so Mr A J Cook becomes A | J | Cook
(| = only to show you they are in seperate columns)
I can use the Concatenate feature to pull the fields to together to make COOkAJ but i have a problem when it comes to names that only have one initial. This puts the name only into two columns and makes the concatenation pull through in the wrong order.
So what i wanted to do is use a Loop to check the third column for any data. If it finds anything in the third (which would be Cook in this example) it concatenates in the following order... 3+1+2 = code else i want it to concatenate in this order ... 2+1 = code
Once it has concatendated it needs to paste into Cell A1 and then move down to cell A2 to run the same loop again until all is done
It then needs to loop until it has no information in all three columns (which would assume it was done)
i really hope that made sense...
Adam
Submitted via EggHeadCafe - Software Developer Portal of Choice
Forms Based Authentication Filtered Content Editor for SharePoint
http://www.eggheadcafe.com/tutorial...c-5aeaa4556c43/forms-based-authenticatio.aspx
sorry if im in the wrong section when asking this, if i am please let me know and i will re post.
I have a spreadsheet with the following columns:
Code
Name - in uppercase
Name - all lower case
Title
Status
CRI Status
The code column is generated by the 4 first characters of the last name and any initials of the previous names.
The Name in upper case is generated from the name in lower case. The name columns have the name in Full such as Mr A J Cook.
I do several other things with the sheet as well which i can do easily but im stuck when it comes to creating the Code field.
I used 'Text to Columns' feature to split the full name into 3 columns. so Mr A J Cook becomes A | J | Cook
(| = only to show you they are in seperate columns)
I can use the Concatenate feature to pull the fields to together to make COOkAJ but i have a problem when it comes to names that only have one initial. This puts the name only into two columns and makes the concatenation pull through in the wrong order.
So what i wanted to do is use a Loop to check the third column for any data. If it finds anything in the third (which would be Cook in this example) it concatenates in the following order... 3+1+2 = code else i want it to concatenate in this order ... 2+1 = code
Once it has concatendated it needs to paste into Cell A1 and then move down to cell A2 to run the same loop again until all is done
It then needs to loop until it has no information in all three columns (which would assume it was done)
i really hope that made sense...
Adam
Submitted via EggHeadCafe - Software Developer Portal of Choice
Forms Based Authentication Filtered Content Editor for SharePoint
http://www.eggheadcafe.com/tutorial...c-5aeaa4556c43/forms-based-authenticatio.aspx