Find and replace characters from the left till first Uppercase

[strooman]

This is my sample data:
(1, ' Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
(16, ' Jane Doe (Spanish)<br> John Doe<br>
(104, ' Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
(19298, ' Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
(189302, ' Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>

The challenge is:
That I want to replace the characters from the left till first Uppercase but keep the left parenthesis. In other words:
Keep the left parenthesis but remove the digit, the comma, the space, the apostrophe and the space at the left.

The result:
(Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
(Jane Doe (Spanish)<br> John Doe<br>
(Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
(Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
(Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>

The problem is the arbitrary length of the digit.

 

[RobP]

Hi, does this get what you need please ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		(1, ' Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
2		(16, ' Jane Doe (Spanish)<br> John Doe<br>
3		(104, ' Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
4		(19298, ' Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
5		(189302, ' Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>
6
7
8
9		(Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
10		(Jane Doe (Spanish)<br> John Doe<br>
11		(Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
12		(Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
13		(Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>
Sheet1

Cell Formulas
Range	Formula
B9:B13	B9	=CONCAT("(",MID(B1,(FIND("'",B1)+2),LEN(B1)))

 

[Fluff]

Another option for 2016

Fluff.xlsm
	A	B
1
2	(1, ' Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>	(Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
3	(16, ' Jane Doe (Spanish)<br> John Doe<br>	(Jane Doe (Spanish)<br> John Doe<br>
4	(104, ' Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>	(Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
5	(19298, ' Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>	(Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
6	(189302, ' Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>	(Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>
Data

Cell Formulas
Range	Formula
B2:B6	B2	=REPLACE(A2,2,FIND("'",A2),"")

 

[RobP]

oops, indeed I need to use CONCATENATE in my formula, rather than CONCAT.. my apologies.

 

[strooman]

Gentlemen, thanks. Both solutions get the job done. This is gonna save me hours (days) of work because it's a very large dataset. 1977000 rows
@RobP, you're correct, I saw already that I had to use CONCATENATE.

 

[RobP]

Good stuff, glad we were able to help.

thanks for the feedback

Rob

 

[Peter_SSs]

because it's a very large dataset. 1977000 row

Is that a typo since Excel only has 1,048,576 rows?

However, since you have a very large data set, what about replacing those values 'in-situ' all at once instead of hundreds of thousands of formulas?

• Select the whole column by clicking its heading label
• Ctrl+H to invoke the Find/Replace dialog
• Find what: "(*' " (but without the double quote marks as I only included them to show that there is a space character after the single quote mark)
• Replace with: (
• Replace All

Before:

24 03 14.xlsm
	B
1	(1, ' Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
2	(16, ' Jane Doe (Spanish)<br> John Doe<br>
3	(104, ' Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
4	(19298, ' Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
5	(189302, ' Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>
6
Replace

After:

24 03 14.xlsm
	B
1	(Lorem ipsum dolor sit amet<br> 3 februari 2024, 20:11<br>
2	(Jane Doe (Spanish)<br> John Doe<br>
3	(Phasellus faucibus scelerisque eleifend donec pretium - Page 4<br>“She sells seashells by the seashore,” a<br>
4	(Sed pulvinar proin gravida hendrerit. - Page 528<br> the quick brown fox jumps over the lazy dog<br>
5	(Filmquiz bracht knappe ex-yogi van de wijs (35 letters), 2e Half 2019<br>12 september 2010, 15:54<br>Foo Bar<br>
6
Replace