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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
It works fine, thank you very much, Snakehips, also for the explaining the difference between UDF and Code!
 
Upvote 0

Forum statistics

Threads
1,211,435
Messages
6,101,849
Members
447,758
Latest member
jojo3strada

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