PDA

View Full Version : Solved: Custom Text Format



hobbiton73
01-16-2013, 09:48 AM
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
01-17-2013, 08:12 AM
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...

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

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
01-17-2013, 08:17 AM
A list of all the managers and a list Validated cell will prevent mis-spellings

hobbiton73
01-17-2013, 10:53 AM
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
01-17-2013, 10:55 AM
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