How can I program a 'roll' of text, eg ABCD - BCDA.

J

Jonny JetSet

Please Help!!!

I would like to use a spreadsheet to program a 'roll' of text.

Examples: ABCD to roll to BCDA and ABCD to roll toCDAB.

I am using Microsoft Office 2003 - Excel 2003.
 
N

Niek Otten

I assume your second ABCD should read BCDA.

=RIGHT(A1,LEN(A1)-1)&LEFT(A1,1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
T

Tom Ogilvy

this only accepts a positive number to roll

Public Function RollText(sStr As String, num As Long)
Dim i As Long, j As Long, k As Long
Dim sStr1 As String
k = Len(sStr)
j = 1 + num Mod k
For i = 1 To Len(sStr)
If j > k Then j = 1
sStr1 = sStr1 & Mid(sStr, j, 1)
j = j + 1
Next
RollText = sStr1
End Function

=rolltext("ABCD",1)
would give BCDA

this doesn't loop:

Public Function RollText1(sStr As String, num As Long)
Dim i As Long, j As Long, k As Long
Dim sStr1 As String
k = Len(sStr)
j = 1 + num Mod k
sStr1 = Right(sStr, k - j + 1) & Left(sStr, j - 1)
RollText1 = sStr1
End Function
 
Top