Edit name Formula if single text with dot take as a word

shrinivasmj

Board Regular
Joined
Aug 29, 2012
Messages
140
hi

i have formula were i need some changes

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(N3)," ",REPT(" ",99)),(1+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>2)+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>4))*99))&","&N3,LEN(N3)))


INPUT IS -Jacqueline M.R. Belanger

OUT PUT IS - BELANGER,JACQUELINE M.R.

i need the out put as R.BELANGER,JACQUELINE M.

single dot in the name EG- M.T. as to be taken as one word and break the name.

Regards
srinivasmj
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,015
How about:

=MID(N3,FIND(".",N3)+1,256) & "," & LEFT(N3,FIND(".",N3))
or
=UPPER(MID(N3,FIND(".",N3)+1,256) & "," & LEFT(N3,FIND(".",N3)))
 
Last edited:

shrinivasmj

Board Regular
Joined
Aug 29, 2012
Messages
140
hi

=UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(N3)," ",REPT(" ",99)),(1+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>2)+(LEN(N3)-LEN(SUBSTITUTE(N3," ",""))>4))*99))&","&N3,LEN(N3)))

the above formula is this way

srinivas shinda singh will come Output- SINGH,SRINIVAS SHINDA upto 3 words will take last single word reverse

srinivas shinda singh kumar Output -SINGH KUMAR,SRINIVAS SHINDA upto 4 to 6 words need to take 2 last words reverse

srinivas shinda singh kumar sons Output-SINGH KUMAR SONS,SRINIVAS SHINDA SINGH above 6 words need to take last 3 words reverse .

this all i am getting in above formula need to get if this single character comes with dot.

Srinivas m.t. OUTPUT T,SRINIVAS M.

Srinivas m.t.i. OUTPUT T.I,SRINIVAS M.

srinivas m.t.i.l.k. OUTPUT I.L.K,SRINIVAS M.T.

need to edit the above formula and add this changes to above formula i need all the out put in one formula
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top