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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
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


Excel 2007
AB
1
2Red Blue White(1) Red (2) Blue (3) White
Sheet4
Cell Formulas
RangeFormula
B2=NumberIt(A2)


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,563
Office Version
  1. 365
  2. 2010
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,632
Messages
5,838,478
Members
430,549
Latest member
jayjay2022

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