Solved: Custom Text Format

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I have been trying to find a solution to this all day online, but I just can't find the answer.

One of the cells (column R) contained within a dynamic form which I've built, allows users to enter their managers name and as you can imagine the results differ in repect of the formats used.

To try and insure a uniformed entry I'd like to have to apply the following format to the cell:

'First Initial' then '.' then the 'Surname'. So for example 'John Smith' would become 'J. Smith'.

Could someone perhaps offer some guidance on the syntax I ned to use for the cell format to achieve this?

Chris

[CodeNinja]

Hobbiton 73,
Assuming they always put the first name / initial at the beginning and the last name at the end, and use spaces between names/initials this formula would work...

[VBA]=REPLACE(TRIM(A1),1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),LEFT(A1,1)&". ")[/VBA]

The formula finds the last space between words (in A1), and replaces it with the first letter in the string and a period.

Hope this works for you.

CodeNinja.

[mikerickson]

A list of all the managers and a list Validated cell will prevent mis-spellings

[hobbiton73]

Hi @CodeNinja, thank you for taking the time to read my post, and for the solution, it's just what I was after. Many thanks and kind regards. Chris

[hobbiton73]

Hi @mikerickson, thank you for spending the time to read my post. I had thought about creating a list of the managers, but because of certain constraints this proved unworkable. Many thanks and kind regards. Chris