Taking the first letter from multiple words in a cell + adding a random number

[jamesdor45]

Hi Guys!

A couple of days I posted a message and you manage to solve my problem! I have another question, which I hope you can help a newbie like me.

I need to create a sequence of letters + numbers that will according to this layout:

Take the first letter from each word showing in cell A1 + take the first letter from each word showing in cell B1. Then I need the system to generate a random number between 1-1000 that will show at the end. So it should show like this:

A1 – fat cat eating
B1 – dog is drinking

I then need to add a random numbers (between 1-1000) to the end. So what I will get in cell C1 is this:

C1 - fcedid876

Can you guys please help me with the formula? I tired playing and searching it all over but without any luck.

Thanks again,
Jerry

 

[iyyi]

If the number of words is not known and you want the random number to remain constant, you will need a UDF.

Function FLandRand(rng As Range)
For Each cl In rng
    n = 1
    For i = 1 To Len(cl)
        If Mid(cl, i, 1) = " " Or i = Len(cl) Then
            FLandRand = FLandRand & Mid(cl, n, 1)
            n = i + 1
        End If
    Next i
Next
FLandRand = FLandRand & Round(Rnd * 1000, 0)
End Function

Now you can use it in C1:
=FLandRand(A1:B1)

 

[jamesdor45]

Hi iyyi,

Thank you so much for the quick follow up and it does work perfect. I didn’t mention that I have a number of numbers of lines in-between. So the actual example should be that I would get the letters from cell A1 and cell D1 (but not the ones from B or C). For example, right now I get the following:

A1 – fat cat eating
B1 - banana
C1 – water bottle
D1 – dog is drinking

So what I get on the designated cell is this – fcebwbdid475. So can you please help so it will only include A and D (and not everything in between).

Thanks so much! TODA!

Regards,
Jerry

 

[iyyi]

Hi iyyi,

Thank you so much for the quick follow up and it does work perfect. I didn’t mention that I have a number of numbers of lines in-between. So the actual example should be that I would get the letters from cell A1 and cell D1 (but not the ones from B or C). For example, right now I get the following:

A1 – fat cat eating
B1 - banana
C1 – water bottle
D1 – dog is drinking

So what I get on the designated cell is this – fcebwbdid475. So can you please help so it will only include A and D (and not everything in between).

Thanks so much! TODA!

Regards,
Jerry

The next code will let you input 1,2 or 3 ranges.

Function FLandRand(rng1 As Range, Optional rng2 As Range, Optional rng3 As Range)
If rng2 Is Nothing And rng3 Is Nothing Then
    Set rng = rng1
ElseIf rng3 Is Nothing Then
    Set rng = Application.Union(rng1, rng2)
Else
  Set rng = Application.Union(rng1, rng2, rng3)
End If


For Each cl In rng
    n = 1
    For i = 1 To Len(cl)
        If Mid(cl, i, 1) = " " Or i = Len(cl) Then
            FLandRand = FLandRand & Mid(cl, n, 1)
            n = i + 1
        End If
    Next i
Next
FLandRand = FLandRand & Round(Rnd * 1000, 0)
End Function

so in your case it would be:
=FLandRand(A1,D1)

Notice that =FLandRand(A1:B1,C1,D1:E1) is valid too

 

[jamesdor45]

THANK YOU SO MUCH! WORKS PERFECT!

Jerry

 

[pgc01]

Hi

For a variable number of single cell arguments, also:

Function FirstLetters(ParamArray vRngs()) As String
Dim vRng As Variant, v As Variant
Dim s As String

For Each vRng In vRngs
    For Each v In Split(vRng.Value)
        s = s & Left(v, 1)
    Next v
Next vRng
FirstLetters= s & Application.WorksheetFunction.RandBetween(0, 999)
End Function

For ex.:

=FirststLetters(A1,C2,F4,B5)