Trying to add spaces between capitalized words with a formula - DoesAnyoneKnowHow?

F

funguy

New Member
Joined
Feb 7, 2012
Messages
24
Hi, I'm trying to find a formula to add spaces in between capitalized words

for example :

cell A1 = MtVernonRoad

trying to make it say..

Mt Vernon Road


...any ideas ? Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Like so perhaps:

Code: 
Option Explicit

Function SplitOnCapital(sText As String) As String
    Dim lCt As Long
    Dim sNewString As String
    Dim sChar As String
    For lCt = 1 To Len(sText)
        sChar = Mid(sText, lCt, 1)
        If UCase(sChar) = sChar Then
            sNewString = sNewString & " " & sChar
        Else
            sNewString = sNewString & sChar
        End If
    Next
    SplitOnCapital = Trim(sNewString)
End Function

Sub demo()
    MsgBox SplitOnCapital("ThisIsAnExampleSentence")
    
End Sub
 
Upvote 0
Here is another UDF you can consider...

Code: 
Function SplitOnCapital(sText As String) As String
  Dim X As Long
  SplitOnCapital = sText
  For X = Len(SplitOnCapital) To 2 Step -1
    If Mid(SplitOnCapital, X - 1, 2) Like "[a-z][A-Z]" Then SplitOnCapital = Application.Replace(SplitOnCapital, X, 0, " ")
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SplitOnCapital just like it was a built-in Excel function. For example,

=SplitOnCapital(A1)
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 641"]
<colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="135" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4937;"> <col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;" span="3"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;" span="8"> <tbody>[TR]
[TD="width: 142, bgcolor: transparent"]Result[/TD]
[TD="width: 135, bgcolor: transparent"]Data[/TD]
[TD="width: 142, bgcolor: transparent"]HelpColumns[/TD]
[TD="width: 142, bgcolor: transparent"] [/TD]
[TD="width: 142, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[TD="width: 19, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad[/TD]
[TD="bgcolor: yellow"]MtVernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[/TR]
</tbody>[/TABLE]


Formulas

Code: 
In A2
=LOOKUP(1,1/LEN($C2:$M2),$C2:$M2)

In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(REPLACE(B2,LARGE(IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),COLUMNS($C2:C2)))>64,
IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),1))<91,ROW(INDIRECT("2:"&LEN(B2))))),COLUMNS($C2:C2)),0," "),"")

And copy to the right.

Markmzz
 
Upvote 0
Markmzz, thanks. I tried this. Seems like a pretty amazing formula , but it only gave me the result with one space (MtVernon Road). In your example , what did you mean I should try putting in D2 ?



markmzz said:
Maybe this:

Layout

[TABLE="width: 641"]
<tbody>[TR]
[TD="width: 142, bgcolor: transparent"]Result[/TD]
[TD="width: 135, bgcolor: transparent"]Data[/TD]
[TD="width: 142, bgcolor: transparent"]HelpColumns[/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad[/TD]
[TD="bgcolor: yellow"]MtVernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[/TR]
</tbody>[/TABLE]



Markmzz
Click to expand...
 
Upvote 0
Markmzz, thanks. I tried this. Seems like a pretty amazing formula , but it only gave me the result with one space (MtVernon Road). In your example , what did you mean I should try putting in D2 ?

markmzz said:
Maybe this:

Layout

[TABLE="width: 641"]
<tbody>[TR]
[TD="width: 142, bgcolor: transparent"]Result[/TD]
[TD="width: 135, bgcolor: transparent"]Data[/TD]
[TD="width: 142, bgcolor: transparent"]HelpColumns[/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad[/TD]
[TD="bgcolor: yellow"]MtVernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[/TR]
</tbody>[/TABLE]


[/Code]

Markmzz
Click to expand...
 
Upvote 0
Thanks for the UDFs guys , But I couldn't try the UDF because I the section where I needed to paste it gave an error. I did some research and it looks like I somehow did not install VBA when I initially installed Excel years back . So, I'll try and remedy that and then try it and report back on those.
 
Upvote 0
funguy said:
Markmzz, thanks. I tried this. Seems like a pretty amazing formula , but it only gave me the result with one space (MtVernon Road). In your example , what did you mean I should try putting in D2 ?
Click to expand...

Try this:

Formulas

Code: 
In A2 - use this

=LOOKUP(1,1/LEN($C2:$M2),$C2:$M2)

In C2 - use this

=IFERROR(REPLACE(B2,LARGE(IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),COLUMNS($C2:C2)))>64,
IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),1))<91,ROW(INDIRECT("2:"&LEN(B2))))),COLUMNS($C2:C2)),0," "),"")

In D2 use this

=IFERROR(REPLACE(C2,LARGE(IF(CODE(MID(C2,ROW(INDIRECT("2:"&LEN(C2))),COLUMNS($C2:D2)))>64,
IF(CODE(MID(C2,ROW(INDIRECT("2:"&LEN(C2))),1))<91,ROW(INDIRECT("2:"&LEN(C2))))),COLUMNS($C2:D2)),0," "),"")

In E2 use this

=IFERROR(REPLACE(D2,LARGE(IF(CODE(MID(D2,ROW(INDIRECT("2:"&LEN(D2))),COLUMNS($C2:E2)))>64,
IF(CODE(MID(D2,ROW(INDIRECT("2:"&LEN(D2))),1))<91,ROW(INDIRECT("2:"&LEN(D2))))),COLUMNS($C2:E2)),0," "),"")

And so on (copy to the right). Then copy the formulas down.

Markmzz
 
Upvote 0
Thanks! it's working amazingly for the 3 word string. But in an instance that there is a 4th word, it creates the spaces, but the first and second word revert to no space. example:

MtVernonRoad becomes Mt Vernon Road
but
MtVernonRoadWest becomes MtVernon Road West
 
Upvote 0
funguy said:
Thanks! it's working amazingly for the 3 word string. But in an instance that there is a 4th word, it creates the spaces, but the first and second word revert to no space. example:

MtVernonRoad becomes Mt Vernon Road
but
MtVernonRoadWest becomes MtVernon Road West
Click to expand...

Look at this:

[TABLE="width: 957"]
<tbody>[TR]
[TD="width: 215, bgcolor: transparent"]Result[/TD]
[TD="width: 204, bgcolor: transparent"]Data[/TD]
[TD="width: 142, bgcolor: transparent"]HelpColumns[/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 142, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad[/TD]
[TD="bgcolor: yellow"]MtVernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Roadbecomes Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoadbecomesMtVernonRoad[/TD]
[TD="bgcolor: yellow"]MtVernonRoadbecomesMtVernon Road[/TD]
[TD="bgcolor: yellow"]MtVernonRoadbecomesMt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernonRoadbecomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernon Roadbecomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Roadbecomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road becomes Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernonRoad becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernonRoad becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernonRoad becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]MtVernon Road becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"]Mt Vernon Road becomes Mt Vernon Road[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"]**[/TD]
[/TR]
</tbody>[/TABLE]


You must to copy the second formula to the right until you want in the sheet. In my example, I copied the second formula until cell AB2.

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
This VBA macro does not work - Why? - (expected to capitalize first letter of first word in sentence, following period & space)
Replies
9
Views
380
abramo
A
L
  • Question Question
Move multiple words in cells into one column with each word it its own cell
Replies
1
Views
287
mumps
mumps
Pinaceous
  • Question Question
vba capitalize 1st letter of each word then exit sub after correction
Replies
3
Views
410
Alex Blakenburg
A
E
  • Solved
How to combine TRIM and LEFT to remove space and extract the first words
Replies
6
Views
760
MountainFog
M
M
  • Question Question
Excel cell value with space between paragraphs with excel to word bookmark
2 3
Replies
24
Views
720
molesy01
M

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top