enter before upper case word

[spirodem]

have a cell containing lots of text and i want to alt enter at the next start of upper case letter
Depth and fast sensing Medium energy to look like below
Depth and fast sensing
Medium energy

 

[kweaver]

And, what haven't you mentioned that doesn't enable you to simply use ALT+enter to do this?

 

[Peter_SSs]

Welcome to the MrExcel board!

I don't know what may possible with your data (always a good idea to give enough samples to demonstrate any variety in your data and expected results).
I presume that you would not want an Alt+E before the red upper case letters here?
Chips are nice McDonalds makes them
Dell computers APPLE phones

 

[Peter_SSs]

You could test this with a copy of your data. I have assumed data to be processed is in column A, starting at row 2.

Sub InsertNewLine()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "( )([A-Z])"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = RX.Replace(a(i, 1), Chr(10) & "$2")
    Next i
    .Value = a
    .Columns.AutoFit
  End With
End Sub

Sample data:

spirodem.xlsm
	A
1	Data
2	Depth and fast sensing Medium energy
3	Chips are nice McDonalds makes them
4
5	No other upper case here
6	Depth and fast sensing Medium energy Long-running
7	Dell computers APPLE phones
Sheet1

Result after code has been run:

spirodem.xlsm
	A
1	Data
2	Depth and fast sensing Medium energy
3	Chips are nice McDonalds makes them
4
5	No other upper case here
6	Depth and fast sensing Medium energy Long-running
7	Dell computers APPLE phones
Sheet1

 

[spirodem]

Welcome to the MrExcel board!

I don't know what may possible with your data (always a good idea to give enough samples to demonstrate any variety in your data and expected results).
I presume that you would not want an Alt+E before the red upper case letters here?
Chips are nice McDonalds makes them
Dell computers APPLE phones

Hi
thanks for the code
I do want the Alt+Enter at the start of each Caps. currently each caps is after a space

 

[spirodem]

Hi
thanks for the code
I do want the Alt+Enter at the start of each Caps. currently each caps is after a space

in this sheet the code is required in column H (h1 is heading)

 

[Peter_SSs]

I do want the Alt+Enter at the start of each Caps. currently each caps is after a space

The first upper case letter is not after a space surely?

in this sheet the code is required in column H (h1 is heading)

So, did you try just changing the references to column A in my code to H?

With Range("A2", Range("A" & Rows.Count).End(xlUp))

 

[spirodem]

The first upper case letter is not after a space surely?
here is the complete cell - others are like this

Depth and fast-response 300 W Medium 95 to 155 energy on targets Low-profile provides good display Recommended for stepped Big housing Also available extra mount Patented ID technology

need it to be shown as

Depth and fast-response 300 W
Medium 95 to 155 energy on targets
Low-profile provides good display
Recommended for stepped
Big housing
Also available extra mount
Patented ID technology

So, did you try just changing the references to column A in my code to H?

With Range("A2", Range("A" & Rows.Count).End(xlUp))

 

[Peter_SSs]

I do want the Alt+Enter at the start of each Caps. currently each caps is after a space

need it to be shown as

Depth and fast-response 300 W

The above contradict each other.

Difficult to provide a good solution unless the "rules" are clear.
We need a water-tight set of rules in words to see if we can then provide code to do it.

 

[spirodem]

The above contradict each other.

Difficult to provide a good solution unless the "rules" are clear.
We need a water-tight set of rules in words to see if we can then provide code to do it.

I cleaned up all the 300 W and now shows 300w
i have also fixed the Patented ID technology to show Patented-ID technology
all it now shows is the
Depth and fast-response 300W Medium 95 to 155 energy on targets etc