How to add a capital letter after a full stop in excel

[Caymen Hammond]

I am trying to make a program that combines two paragraphs or sentences together. I have got it as far a combining the sentences and putting a space after the full stop, but am struggling to get the first letter after the full stop to be capital, can somone point me in the right direction please.

 

[Peter_SSs]

Welcome to the MrExcel board!

It is hard to know what you have and exactly what you are trying to achieve. For the future, note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this, or any parts of it are any use to you.

21 06 20.xlsm
	A
1	the cat sat on the mat
2	THE MOUSE RAN AWAY
3	The cat sat on the mat. The mouse ran away.
Sentences

Cell Formulas
Range	Formula
A3	A3	=UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1)))&". "&UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))&"."

 

[Caymen Hammond]

Here is a picture of the text I am using to test the formulas and a copy of the formulas used to try and get the excel program to work properly.

 

[Radoslaw Poprawski]

did you Tried =Proper() function?

if you want it as in correct senntence you would have adjust this formula:

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	D	E	F
31	this is my sentence :)	This Is My Sentence :)	This is my sentence :)
32	hello unfriendly data	Hello Unfriendly Data	Hello unfriendly data
33	it is just non capitalized sentence	It Is Just Non Capitalized Sentence	It is just non capitalized sentence
Sheet5

Cell Formulas
Range	Formula
E31:E33	E31	=PROPER($D$31:$D$33)
F31:F33	F31	=UPPER(LEFT($D$31:$D$33,1))&LOWER(RIGHT($D$31:$D$33,LEN($D$31:$D$33)-1))
Dynamic array formulas.

 

[Caymen Hammond]

Welcome to the MrExcel board!

It is hard to know what you have and exactly what you are trying to achieve. For the future, note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if this, or any parts of it are any use to you.

21 06 20.xlsm
	A
1	the cat sat on the mat
2	THE MOUSE RAN AWAY
3	The cat sat on the mat. The mouse ran away.
Sentences

Cell Formulas
Range	Formula
A3	A3	=UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1)))&". "&UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))&"."

Here is a link to the file I am working on right now Notes.xlsx

did you Tried =Proper() function?

if you want it as in correct senntence you would have adjust this formula:

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	D	E	F
31	this is my sentence :)	This Is My Sentence :)	This is my sentence :)
32	hello unfriendly data	Hello Unfriendly Data	Hello unfriendly data
33	it is just non capitalized sentence	It Is Just Non Capitalized Sentence	It is just non capitalized sentence
Sheet5

Cell Formulas
Range	Formula
E31:E33	E31	=PROPER($D$31:$D$33)
F31:F33	F31	=UPPER(LEFT($D$31:$D$33,1))&LOWER(RIGHT($D$31:$D$33,LEN($D$31:$D$33)-1))
Dynamic array formulas.

 

[Radoslaw Poprawski]

Here are the basics:

Notes.xlsx
	A	B
1	this is a test. This is a test
2
3	Splitting the text	this is a test.
4		This is a test
5
6	first letter	T
7		T
8
9	rest of the sentence:	his is a test.
10		his is a test
11
12	Tougheter:	This is a test.
13		This is a test.
14
15	Like in a Word:	This is a test. This is a test.
Sheet2

Cell Formulas
Range	Formula
B3	B3	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
B4	B4	=TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999))
B6:B7	B6	=UPPER(LEFT(B3,1))
B9:B10	B9	=LOWER(RIGHT(B3,LEN(B3)-1))
B12:B13	B12	=IF(RIGHT(CONCAT(B6&B9),1)=".",CONCAT(B6&B9),CONCAT(B6&B9)&".")
B15	B15	=TEXTJOIN(" ",TRUE,B12,B13)

now to make it in 1 cell formula
BRB.

 

[Caymen Hammond]

Here are the basics:

Notes.xlsx
	A	B
1	this is a test. This is a test
2
3	Splitting the text	this is a test.
4		This is a test
5
6	first letter	T
7		T
8
9	rest of the sentence:	his is a test.
10		his is a test
11
12	Tougheter:	This is a test.
13		This is a test.
14
15	Like in a Word:	This is a test. This is a test.
Sheet2

Cell Formulas
Range	Formula
B3	B3	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
B4	B4	=TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999))
B6:B7	B6	=UPPER(LEFT(B3,1))
B9:B10	B9	=LOWER(RIGHT(B3,LEN(B3)-1))
B12:B13	B12	=IF(RIGHT(CONCAT(B6&B9),1)=".",CONCAT(B6&B9),CONCAT(B6&B9)&".")
B15	B15	=TEXTJOIN(" ",TRUE,B12,B13)

now to make it in 1 cell formula
BRB.

Thanks for looking into this for me mate. Once this works with one formula then let me know so I can see if it will work with a real example piece of text. Can you also link me to the workbook you are working on please?

 

[Radoslaw Poprawski]

Here u go
Final formula in B17, as you wished in 1 cell

Notes.xlsx
	A	B
1	this is a test. This is a test
2
3	Splitting the text	this is a test.
4		This is a test
5
6	first letter	T
7		T
8
9	rest of the sentence:	his is a test.
10		his is a test
11
12	Tougheter:	This is a test.
13		This is a test.
14
15	Like in a Word:	This is a test. This is a test.
16
17	Like in word: 1 cell formula:	This is a test. This is a test.
Sheet2

Cell Formulas
Range	Formula
B3	B3	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
B4	B4	=TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999))
B6:B7	B6	=UPPER(LEFT(B3,1))
B9:B10	B9	=LOWER(RIGHT(B3,LEN(B3)-1))
B12:B13	B12	=IF(RIGHT(CONCAT(B6&B9),1)=".",CONCAT(B6&B9),CONCAT(B6&B9)&".")
B15	B15	=TEXTJOIN(" ",TRUE,B12,B13)
B17	B17	=LET( LeftText,LEFT(A1,FIND(CHAR(10),A1,1)-1), RightText,TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999)), FirstLetLeftText,UPPER(LEFT(LEFT(LeftText,1))), FirstLetRightText,UPPER(LEFT(RightText,1)), RightPartLeftText,RIGHT(LeftText,LEN(LeftText)-1), RightPartRightText,RIGHT(RightText,LEN(RightText)-1), DotCheckLeftText,IF(RIGHT(RightPartLeftText,1)=".",RightPartLeftText,RightPartLeftText&"."), DotCheckRightText,IF(RIGHT(RightPartRightText,1)=".",RightPartRightText,RightPartRightText&"."), FinalLeftText,FirstLetLeftText&DotCheckLeftText, FinalRightText,FirstLetRightText&DotCheckRightText, FinalText,FinalLeftText&" "&FinalRightText, FinalText)

 

[Caymen Hammond]

Here u go
Final formula in B17, as you wished in 1 cell

Notes.xlsx
	A	B
1	this is a test. This is a test
2
3	Splitting the text	this is a test.
4		This is a test
5
6	first letter	T
7		T
8
9	rest of the sentence:	his is a test.
10		his is a test
11
12	Tougheter:	This is a test.
13		This is a test.
14
15	Like in a Word:	This is a test. This is a test.
16
17	Like in word: 1 cell formula:	This is a test. This is a test.
Sheet2

Cell Formulas
Range	Formula
B3	B3	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
B4	B4	=TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999))
B6:B7	B6	=UPPER(LEFT(B3,1))
B9:B10	B9	=LOWER(RIGHT(B3,LEN(B3)-1))
B12:B13	B12	=IF(RIGHT(CONCAT(B6&B9),1)=".",CONCAT(B6&B9),CONCAT(B6&B9)&".")
B15	B15	=TEXTJOIN(" ",TRUE,B12,B13)
B17	B17	=LET( LeftText,LEFT(A1,FIND(CHAR(10),A1,1)-1), RightText,TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999)), FirstLetLeftText,UPPER(LEFT(LEFT(LeftText,1))), FirstLetRightText,UPPER(LEFT(RightText,1)), RightPartLeftText,RIGHT(LeftText,LEN(LeftText)-1), RightPartRightText,RIGHT(RightText,LEN(RightText)-1), DotCheckLeftText,IF(RIGHT(RightPartLeftText,1)=".",RightPartLeftText,RightPartLeftText&"."), DotCheckRightText,IF(RIGHT(RightPartRightText,1)=".",RightPartRightText,RightPartRightText&"."), FinalLeftText,FirstLetLeftText&DotCheckLeftText, FinalRightText,FirstLetRightText&DotCheckRightText, FinalText,FinalLeftText&" "&FinalRightText, FinalText)

Does the spreadsheet need to be set out like yours or can I just have an input box and then use the formula from b17 in the output box.

 

[Caymen Hammond]

Here u go
Final formula in B17, as you wished in 1 cell

Notes.xlsx
	A	B
1	this is a test. This is a test
2
3	Splitting the text	this is a test.
4		This is a test
5
6	first letter	T
7		T
8
9	rest of the sentence:	his is a test.
10		his is a test
11
12	Tougheter:	This is a test.
13		This is a test.
14
15	Like in a Word:	This is a test. This is a test.
16
17	Like in word: 1 cell formula:	This is a test. This is a test.
Sheet2

Cell Formulas
Range	Formula
B3	B3	=LEFT(A1,FIND(CHAR(10),A1,1)-1)
B4	B4	=TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999))
B6:B7	B6	=UPPER(LEFT(B3,1))
B9:B10	B9	=LOWER(RIGHT(B3,LEN(B3)-1))
B12:B13	B12	=IF(RIGHT(CONCAT(B6&B9),1)=".",CONCAT(B6&B9),CONCAT(B6&B9)&".")
B15	B15	=TEXTJOIN(" ",TRUE,B12,B13)
B17	B17	=LET( LeftText,LEFT(A1,FIND(CHAR(10),A1,1)-1), RightText,TRIM(MID(A1,FIND(CHAR(10),A1,1)+2,9999)), FirstLetLeftText,UPPER(LEFT(LEFT(LeftText,1))), FirstLetRightText,UPPER(LEFT(RightText,1)), RightPartLeftText,RIGHT(LeftText,LEN(LeftText)-1), RightPartRightText,RIGHT(RightText,LEN(RightText)-1), DotCheckLeftText,IF(RIGHT(RightPartLeftText,1)=".",RightPartLeftText,RightPartLeftText&"."), DotCheckRightText,IF(RIGHT(RightPartRightText,1)=".",RightPartRightText,RightPartRightText&"."), FinalLeftText,FirstLetLeftText&DotCheckLeftText, FinalRightText,FirstLetRightText&DotCheckRightText, FinalText,FinalLeftText&" "&FinalRightText, FinalText)

Having problems with your formula in B37 as it only did part of the conversions

Can you help please?