CODEWITHSUNDEEP
excelvba
Hsb
Hsb

Reputation: 21

how to insert numbers before every word in excel

http://imgur.com/ekrjv0D

Hi please check above image. Is there any way to insert number before each word in worksheet.
I have a table like this:

╔════════════╦═══════╗
║     Colour ║       ║
╠════════════╬═══════╣
║            ║ Blue  ║
╠════════════╬═══════╣
║            ║ red   ║
╠════════════╬═══════╣
║            ║ pink  ║
╠════════════╬═══════╣
║     Fruidm ║       ║
╠════════════╬═══════╣
║            ║ mango ║
╠════════════╬═══════╣
║            ║ Apple ║
╚════════════╩═══════╝

Output should be like this:

╔══════════════╦═════════╗
║     1/Colour ║         ║
╠══════════════╬═════════╣
║              ║ 2/Blue  ║
╠══════════════╬═════════╣
║              ║ 3/red   ║
╠══════════════╬═════════╣
║              ║ 4/pink  ║
╠══════════════╬═════════╣
║     5/Fruidm ║         ║
╠══════════════╬═════════╣
║              ║ 6/mango ║
╠══════════════╬═════════╣
║              ║ 7/Apple ║
╚══════════════╩═════════╝

or like this:

╔══════════════╦═════════╗
║     1/Colour ║         ║
╠══════════════╬═════════╣
║              ║ 3/Blue  ║
╠══════════════╬═════════╣
║              ║ 4/red   ║
╠══════════════╬═════════╣
║              ║ 5/pink  ║
╠══════════════╬═════════╣
║     2/Fruidm ║         ║
╠══════════════╬═════════╣
║              ║ 6/mango ║
╠══════════════╬═════════╣
║              ║ 7/Apple ║
╚══════════════╩═════════╝

Upvotes: 1

Views: 141

Answers (2)

Vasily
Vasily

Reputation: 5782

another additional variant to already posted:

Sub test()
    Dim cl As Range, i&
    Set cl = Cells.Find("*")
    For i = 1 To WorksheetFunction.CountA(Cells)
        If Not cl Is Nothing Then
            cl.Value2 = i & "/" & cl.Value2
            Set cl = Cells.FindNext(cl)
        Else
            Exit For
        End If
    Next i
End Sub

updated against additional question:

Is there anyway to remove back the numbers and slash at the beginning of each word

you can use this:

Sub test2()
   Dim n&, r As Range:  n = 1
   For Each r In ActiveSheet.UsedRange
      If r.Value2 Like "*/*" Then
         r.Value2 = Split(r.Value2, "/")(1)
         n = n + 1
      End If
   Next r
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

Try this short macro:

Sub marine()
   Dim n As Long
   Dim r As Range
   n = 1

   For Each r In ActiveSheet.UsedRange
      If r.Value <> "" Then
         r.Value = n & "/" & r.Value
         n = n + 1
      End If
   Next r
End Sub

Upvotes: 1

Related Questions