How to copy words from A1 to B1 and adding the text (1), (2), (3), etc., in front of the first, second, third, etc., word in B1

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Hello Forumers,

In cell A1 I have the following content:

Red Blue White

In cell B1 I would like to have the following content:

(1) Red (2) Blue (3) White

The macro should work for any number of words in cell A1.

Any solution? And is it possible to have the added parts (1), (2), (3) etc. displayed in a particular font, say, a small font Times Roman 9 points, if the rest of the content in A1 (Red, Blue, White) would be, for instance, Times Roman 14 points, or is that asking too much, programmingwise ;) ?

Thank you in advance,
Harry
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,113
Office Version
  1. 2013
Platform
  1. Windows
Harry,

This udf will give you the revised text bar for the formatting.
Copy to a code module.
Code:
Function NumberIt(Rng As Range)
Arry = Split(Rng)
    For i = 0 To UBound(Arry)
        Arry(i) = "(" & i + 1 & ") " & Arry(i)
    Next i
NumberIt = Join(Arry, " ")
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Red Blue White</td><td style=";">(1) Red (2) Blue (3) White</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=NumberIt(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

If you don't want a udf then a variant of the below sub will do same.

Code:
Sub Number_It()
Arry = Split(Range("A2"))


For i = 0 To UBound(Arry)
Arry(i) = "(" & i + 1 & ") " & Arry(i)
Next i
Range("A2").Offset(0, 1) = Join(Arry, " ")


End Sub

Hope that helps.
 

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
Harry,

This udf will give you the revised text bar for the formatting.
Copy to a code module.
Code:
Function NumberIt(Rng As Range)
Arry = Split(Rng)
    For i = 0 To UBound(Arry)
        Arry(i) = "(" & i + 1 & ") " & Arry(i)
    Next i
NumberIt = Join(Arry, " ")
End Function

Excel 2007
AB
1
2Red Blue White(1) Red (2) Blue (3) White

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
B2=NumberIt(A2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



If you don't want a udf then a variant of the below sub will do same.

Code:
Sub Number_It()
Arry = Split(Range("A2"))


For i = 0 To UBound(Arry)
Arry(i) = "(" & i + 1 & ") " & Arry(i)
Next i
Range("A2").Offset(0, 1) = Join(Arry, " ")


End Sub

Hope that helps.

Thank you very much, Snakehips, I will check it out and let you know if it works (no doubt about that). PS: What is a UDF?
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,113
Office Version
  1. 2013
Platform
  1. Windows
Thank you very much, Snakehips, I will check it out and let you know if it works (no doubt about that). PS: What is a UDF?

You are welcome.
UDF is a User Defined Function.

i.e. you can write code to create your own custom Function, with defined arguments/parameters. Then you use it in formulas just like any standard Excel function.
The alternative is to have a Sub-routine of code which you run, by some means, e.g. button click or keyboard shortcut, as and when it suits your need.
 

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
It works fine, thank you very much, Snakehips, also for the explaining the difference between UDF and Code!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,683
Members
416,040
Latest member
patriciocabello

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
Top