Move part of a sentence into a new column

[esb128]

Hello,

I need some help, i have a large database of product data, im looking to move all the data after the last " . " into a new column, an example is below. After the last period is specifications which is what i need to move over (note there could be more sentences then this, i need the formula to look at the last period in the paragraph and move that data.

Problem

Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate. No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1

Outcome

Column A

Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate.

Column B

No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1

thank you very much in advance.

Evan

 

[JamesCanale]

For the formula in C1, you might want to "TRIM" it to remove the leading space if that's a problem. I have it looking for period-space. That will fix any decimal places in the text.

MrExcelPlayground19.xlsx
	A	B	C
1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate. No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate.	No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1
Sheet20

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,MAX(IFERROR(SEARCH(". ",A1,SEQUENCE(LEN(A1))),0)))
C1	C1	=RIGHT(A1,LEN(A1)-MAX(IFERROR(SEARCH(". ",A1,SEQUENCE(LEN(A1))),0)))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1
2	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate. No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate.	No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1
Data

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,".",-1)&"."
C2	C2	=TRIM(TEXTAFTER(A2,".",-1))

 

[JamesCanale]

You can say "A better option". I'm old and keep forgetting the new formulas that make everything easier.

 

[Fluff]

I don't really believe in "better", just different ways.

 

[lrobbo314]

Nice, Fluff. I've just been using TEXTBEFORE for simple stuff and totally overlooked the 'Instance' argument.

Another tool on the belt!

 

[esb128]

For the formula in C1, you might want to "TRIM" it to remove the leading space if that's a problem. I have it looking for period-space. That will fix any decimal places in the text.

MrExcelPlayground19.xlsx
	A	B	C
1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate. No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate.	No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1
Sheet20

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,MAX(IFERROR(SEARCH(". ",A1,SEQUENCE(LEN(A1))),0)))
C1	C1	=RIGHT(A1,LEN(A1)-MAX(IFERROR(SEARCH(". ",A1,SEQUENCE(LEN(A1))),0)))

amazing thank you very much!

 

[esb128]

Another option

Fluff.xlsm
	A	B	C
1
2	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate. No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1	Bearing plates give greater bearing surface than standard cut washers and help distribute the load at these critical connections. 3" x 3" bearing plates offer increased flexibility while meeting the latest code requirements. The slotted hole allows for adjustability to account for bolts that are not in the middle of the sill plate.	No: BPS 1/2-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 1/2 In diameter bolt, Pkg Qty: 1 No: BPS 5/8-3HDG: Size: 3 In x 3 In, Material: Steel, Finish: Hot Dipped Galvanized, Install with: 5/8 In diameter bolt, Pkg Qty: 1
Data

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,".",-1)&"."
C2	C2	=TRIM(TEXTAFTER(A2,".",-1))

",A1,SEQUENCE(LEN(A1))),0)))[/XD][/XR][/RANGE]

amazing thank you very much! for both formulas they work perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.