PDA

View Full Version : Create a numbered list from string



laraerin
07-06-2016, 06:05 PM
Hi Everyone,


Using VBA, I want to transform this:

Exchange or install a total of 7 fire extinguishers. 1. Office rear kitchen - 1 x 3.5kg Co2 requires replacing as it has rust and around the cylinder and on the trigger - (unsafe) 2. Ventilation room level 2 carpark -1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date. 3. Store room ADJT ventilation room – 1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date. 4. Switch Board room in carpark - 1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date. 5. Level 6 - 1 x 3.5 kg Co2 out of date requires replacing as its out of date. 6. Sugarcane Restaurant - 1 x 7 Ltr wetcam extinguishers for fat fire out of date - (requires replacing with new wetcam) 7. Sugarcane Restaurant - 1 x 5kg Co2 1 year out of date requires replacing with new C02 and signage.

into this:

Exchange or install a total of 7 fire extinguishers.
1. Office rear kitchen - 1 x 3.5kg Co2 requires replacing as it has rust and around the cylinder and on the trigger (unsafe)
2. Ventilation room level 2 carpark - 1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date.
3. Store room ADJT ventilation room - 1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date.
4. Switch Board room in carpark - 1 x 5kg Co2 has not been pressure tested since 2009 requires replacing as its 2 years out of date.
5. Level 6 - 1 x 3.5 kg Co2 out of date requires replacing as its out of date.
6. Sugarcane Restaurant - 1 x 7 Ltr wetcam extinguishers for fat fire out of date – (requires replacing with new wetcam)
7. Sugarcane Restaurant - 1 x 5kg Co2 1 year out of date requires replacing with new C02 and signage.

List numbers will always be in the format <space>[0-9].<space>
String is in A1

NOTE: Lists can go to double digits.

Any ideas?
Thanks in advance.

offthelip
07-06-2016, 10:37 PM
I am assuming the big long string is in A1 and it putsthe list in column B, it assumes thatn all numbers are present (ie. no missing numbers)
try this;

Sub stringtolist()
inarr = Cells(1, 1)
strt = 1
last = Len(inarr)
For i = 1 To 99
serch = " " & i & ". "
endstr = InStr(strt, inarr, serch, vbTextCompare)
If endstr > 0 Then
Cells(i, 2) = Mid(inarr, strt, endstr - strt)
strt = endstr
Else
endstr = last
Cells(i, 2) = Mid(inarr, strt, endstr - strt)
Exit For
End If
Next i
End Sub

snb
07-07-2016, 03:32 AM
Please post a sample file !

offthelip
07-07-2016, 10:42 AM
Why ask for a sample file? the question is perfectly clear without: A string in A1 to be converted into a list, fairly obviously in sucessive rows somewhere.
I believe my solution does exactly that!!

jolivanes
07-09-2016, 10:18 PM
@offthelip
Did you test your code?

offthelip
07-10-2016, 01:49 AM
16587

yes I did, see attached

Paul_Hossler
07-10-2016, 06:33 AM
another way



Option Explicit

Sub StringToList()
Dim sIn As String
Dim i As Long
Dim a() As String

With ActiveSheet
sIn = .Cells(1, 1).Value

For i = 99 To 1 Step -1
sIn = Replace(sIn, " " & i & ". ", vbCrLf & i & ". ")
Next I

a = Split(sIn, vbCrLf)
.Cells(1, 2).Resize(UBound(a) + 1, 1).Value = Application.WorksheetFunction.Transpose(a)

End With
End Sub