On Mon, 11 Jan 2010 02:26:15 -0800, adam cook wrote:

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



There's no need to do the Text-to-Columns to generate the code.

How do you get rid of the title (e.g. Mr) when you do the text-to-columns?
Do all names have a title, or do you have to test for it?

You could use something like the code below to generate the code. See comments
in line for how to handle the Title:

=======================================
Option Explicit
Function NameCode(FullName As String) As String
Dim aName As Variant
Dim i As Long

aName = Split(WorksheetFunction.Trim(UCase(FullName)))
NameCode = Left(aName(UBound(aName)), 4)

'start loop at 1 if there is a Title
'otherwise start loop at 0

For i = 1 To UBound(aName) - 1
NameCode = NameCode & Left(aName(i), 1)
Next i

End Function
======================================

--ron