Stripping out regex strings out of a range

[sdspieg]

2805085 (1).xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI
3	Q6.1.1. Future Russia - Putinism forever	Q6.1.2. Future Russia - Raspad	Q6.1.3. Future Russia - (Re-?) Turn to Europe	Q6.1.4. Future Russia - Stalinism on AI Steroids	Q6.2.1. Future Russia - Putinism forever	Q6.2.2. Future Russia - Raspad	Q6.2.3. Future Russia - (Re-?) Turn to Europe	Q6.2.4. Future Russia - Stalinism on AI Steroids	Q6.3.1. Future Russia - Putinism forever	Q6.3.2. Future Russia - Raspad	Q6.3.3. Future Russia - (Re-?) Turn to Europe	Q6.3.4. Future Russia - Stalinism on AI Steroids	Q6.4.1. Future Russia - Putinism forever	Q6.4.2. Future Russia - Raspad	Q6.4.3. Future Russia - (Re-?) Turn to Europe	Q6.4.4. Future Russia - Stalinism on AI Steroids	Q6.5.1. Future Russia - Putinism forever	Q6.5.2. Future Russia - Raspad	Q6.5.3. Future Russia - (Re-?) Turn to Europe	Q6.5.4. Future Russia - Stalinism on AI Steroids	Q6.6.1. Future Russia - Putinism forever	Q6.6.2. Future Russia - Raspad	Q6.6.3. Future Russia - (Re-?) Turn to Europe	Q6.6.4. Future Russia - Stalinism on AI Steroids	Q7.1.1. Diplomatic engagement	Q7.1.2. Economic engagement (positive and/or negative stimuli)	Q7.1.3. Targeted information outreach	Q7.2.1. Diplomatic engagement	Q7.2.2. Economic engagement (positive and/or negative stimuli)	Q7.2.3. Targeted information outreach	Q7.3.1. Diplomatic engagement	Q7.3.2. Economic engagement (positive and/or negative stimuli)	Q7.3.3. Targeted information outreach	Q7.4.1. Diplomatic engagement	Q7.4.2. Economic engagement (positive and/or negative stimuli)	Q7.4.3. Targeted information outreach	Q7.5.1. Diplomatic engagement	Q7.5.2. Economic engagement (positive and/or negative stimuli)	Q7.5.3. Targeted information outreach	Q7.6.1. Diplomatic engagement	Q7.6.2. Economic engagement (positive and/or negative stimuli)	Q7.6.3. Targeted information outreach	Q7.7.1. Diplomatic engagement	Q7.7.2. Economic engagement (positive and/or negative stimuli)	Q7.7.3. Targeted information outreach	Q8.1.1. Acceptability for Europe	Q8.1.2. Desirability for Europe	Q8.1.3. Receptiveness of the Russian 'targets'	Q8.1.4. Expected effectiveness	Q8.2.1. Acceptability for Europe	Q8.2.2. Desirability for Europe	Q8.2.3. Receptiveness of the Russian 'targets'	Q8.2.4. Expected effectiveness	Q8.3.1. Acceptability for Europe	Q8.3.2. Desirability for Europe	Q8.3.3. Receptiveness of the Russian 'targets'	Q8.3.4. Expected effectiveness	Q8.4.1. Acceptability for Europe	Q8.4.2. Desirability for Europe	Q8.4.3. Receptiveness of the Russian 'targets'	Q8.4.4. Expected effectiveness	Q8.5.1. Acceptability for Europe	Q8.5.2. Desirability for Europe	Q8.5.3. Receptiveness of the Russian 'targets'	Q8.5.4. Expected effectiveness	Q8.6.1. Acceptability for Europe	Q8.6.2. Desirability for Europe	Q8.6.3. Receptiveness of the Russian 'targets'	Q8.6.4. Expected effectiveness	Q8.7.1. Acceptability for Europe	Q8.7.2. Desirability for Europe	Q8.7.3. Receptiveness of the Russian 'targets'	Q8.7.4. Expected effectiveness	Q9.1.1. Immediately	Q9.1.2. Medium-term	Q9.1.3. Long-term	Q9.2.1. Immediately	Q9.2.2. Medium-term	Q9.2.3. Long-term	Q9.3.1. Immediately	Q9.3.2. Medium-term	Q9.3.3. Long-term	Q9.4.1. Immediately	Q9.4.2. Medium-term	Q9.4.3. Long-term	Q9.5.1. Immediately	Q9.5.2. Medium-term	Q9.5.3. Long-term	Q10.1.1. 2025	Q10.1.2. 2030	Q10.1.3. 2040	Q10.2.1. 2025	Q10.2.2. 2030	Q10.2.3. 2040	Q10.3.1. 2025	Q10.3.2. 2030	Q10.3.3. 2040	Q10.4.1. 2025	Q10.4.2. 2030	Q10.4.3. 2040
Export + Quant-Likert - Qs

I am trying to strip all strings like "Q8. ", "Q10.2. " or "Q7.4.2 " out of range of cells like the ones I'm sharing here.

I tried this, but it didn't do anything

Sub RemoveRegexStrings()
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.Selection
    On Error GoTo 0
    If rng Is Nothing Then
        Set rng = Application.InputBox("Please select the range:", Type:=8)
    End If

    Dim cell As Range
    For Each cell In rng
        cell.Value = Trim(Replace(cell.Value, "Q[0-9]*(\.[0-9]*)*\.", ""))
        Next cell
End Sub

As always, dear MrExcel wizards: Help!

 

[Rick Rothstein]

If the only dots in your text occurs in the Q-number at the beginning of the text, and nowhere else, then you can do what you want without using a macro. Select all the cells you want to do this to, press CTRL+H to bring up the Replace dialog box, put this...

Q*. <== Note there is a single space character after the dot

in the "Find what" field, leave the "Replace with" field empty, and then click the "Replace All" button.

 

[sdspieg]

If the only dots in your text occurs in the Q-number at the beginning of the text, and nowhere else, then you can do what you want without using a macro. Select all the cells you want to do this to, press CTRL+H to bring up the Replace dialog box, put this...

Q*. <== Note there is a single space character after the dot

in the "Find what" field, leave the "Replace with" field empty, and then click the "Replace All" button.

Thanks Rick. That is - alas, not the case though. I only shared a small part of the spreadsheet, but other parts do have dots. But surely this should be regexable, no?

 

[Rick Rothstein]

Thanks Rick. That is - alas, not the case though. I only shared a small part of the spreadsheet, but other parts do have dots. But surely this should be regexable, no?

I do not do RegEx coding, so I cannot help on that part; however, this replacement for your For..Next loop should make your code do what you want...

  For Each Cell In Selection
    If Cell.Value Like "Q#*. *" Then Cell.Value = Mid(Cell.Value, InStr(Cell.Value, ". ") + 2)
  Next

 

[sdspieg]

Thanks Rick!