Removing blank spaces from the beginning of cells

[Research RN]

I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.

I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.


If patient “John R. Smith”, for example, is entered in the database as”

“Smith” “John” “R “

and also as

“Smith” “<space>John” “R”


then the database won’t recognize them as the same name when I search for John Smith’s data. Right?

If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.

[martindwilson]

trim(a1) will do it
or an array entered vlookup will do them in situ
{=VLOOKUP(D1,TRIM($A$1:$B$2),2,FALSE)}

[Research RN]

trim(a1) will do it
or an array entered vlookup will do them in situ
{=VLOOKUP(D1,TRIM($A$1:$B$2),2,FALSE)}

Works like a charm!

Thanks, Martin