reconstruction of a string in vba

[EDUCATED MONKEY]

This is what i start with 1. Winnie-the-Pooh the object is to get rid of the 1. bit, to leave Winnie-the-Pooh I found this code and mod it to fit my needs <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The problem now is to put back together the new string at the bottom of the code I have experimented and found that I could manualy reconstruct the string as required. I as was able to find the length of the string and I can also see that if I used a loop to look for a space char then each word word be reconstructed, I have run out of ideas how to do this progamicaly, please note that the items to be remove vairy and I have no difficulty in take the orignal string apart it the putting back together were I need your help<o></o>
<o> </o>
Sub LoopThroughString()<o></o>
<o> </o>
Dim n As Integer<o></o>
Dim Counter As Integer<o></o>
Dim MyString As String<o></o>
Dim NewString As String<o></o>
Dim w As Variant<o></o>
<o> </o>
MyString = Worksheets("REMOVE UNWANTED ITEMS").Range("B1").Value<o></o>
<o> </o>
n = Len(MyString)<o></o>
<o> </o>
For Counter = 1 To Len(MyString)<o></o>
<o></o>
Worksheets("SPLITWORD").Select<o></o>
w = Mid(MyString, Counter, 1)<o></o>
<o> </o>
If w = 1 Or w = "." Then<o></o>
w = " "<o></o>
End If<o></o>
Cells(1, Counter) = w<o></o>
<o></o>
Next<o></o>
NewString = Cells(1, 4) + Cells(1, 5) + Cells(1, 6) + Cells(1, 7)<o></o>
<o> </o>
For z = 4 To 7<o></o>
NewString = Cells(1, z)<o></o>
<o> </o>
Next<o></o>
<o></o>
Cells(2, 2) = NewString<o></o>
<o> </o>
End Sub

 

[AlphaFrog]

Sub test()
Worksheets("SPLITWORD").Cells(2, 2).Value = Trim(Replace(Worksheets("REMOVE UNWANTED ITEMS").Range("B1").Value, "1.", ""))
End Sub

 

[Greg Truby]

Welcome to the board. Yer sure going all the way around the barn just to get from one stall to the next. All you'd need is something like so:

Sub SillyOldPooh()
    Dim s$
 
    With ActiveCell
        s = .Value
        .Value = Right$(s, Len(s) - InStr(1, s, " "))
    End With
End Sub

Which would work on that and
2. Tigger
3. Piglet
4. Rabbit
5. Christopher Robin

etc., etc.

 

[EDUCATED MONKEY]

thank you for your replyto be honest not totally sure i get what all of it does but i learn by experimentaion and the help from good people like your self, i had thought of using an array to hold the data and pull it out as required i wil read through what you have been good enough to send and use it and then try to understand what is going on so the i gain something other then a working function thanks pete

 

[EDUCATED MONKEY]

Thank you for your reply it seems that some how i havd given the impression that i need to break the original string. the problem is not that i need to put it back together having removed the items that are unwanted which change so i will need to creat a if or list of them to accumplish the deconstruction over the whole range of possibilities, i have allready used the trim function but as far as i can see it only remove spaces unless i have missed the point of trim regards pete

 

[AlphaFrog]

There are a variety of tools and methods to parse text strings in VBA. Could you better explain the nature of your original text strings and what you want to remove?

Do you always want to remove the leading number and period or could the numbers be located anywhere in the string?

Do you want to strip for any number and period e.g. 1. 2. 10. 15. etc.

Do you have a list of items you want to strip? If yes what?

The more details you could provide, the more specific help you'll likely get.

 

[EDUCATED MONKEY]

hope this explains why I am trying to do the one on the left is a typical data input while the one on the right is what is required I have the understanding how to check each element of the string and I have the string displayed across a row. so I can say if item 1 is a number remove it by replacing it by a space similarly I can say if item 2 is a period replace that with a space. Then I can trim the lot part of job done since everything beyond the bracket is not required this can be a simple find(“(“) then <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
mid((title ) value from find for start at and len(title)- start at I have almost got this to work with excel function but need some more flexability as there are variations that can not be covered thus vba <o></o>
<TABLE style="MARGIN: auto 6.75pt; WIDTH: 187.45pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: margin; mso-table-left: left; mso-table-top: 3.05pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=250 align=left><TBODY><TR style="HEIGHT: 13.85pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.85pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap>The House-Warming <o></o>
</TD></TR><TR style="HEIGHT: 13.85pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.85pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap> The Rape of the Sherlock (1903) <o></o>
</TD></TR><TR style="HEIGHT: 13.85pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.85pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap> aka cold day <o></o>
</TD></TR><TR style="HEIGHT: 13.85pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.85pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap>1. When We Were Very Young (1924)<o></o>
</TD></TR><TR style="HEIGHT: 13.85pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.85pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap>2. Winnie-the-Pooh (1926)<o></o>
</TD></TR><TR style="HEIGHT: 14.65pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 14.65pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap>3. Now We Are Six (1927)<o></o>
</TD></TR><TR style="HEIGHT: 15.5pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 187.45pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 15.5pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=250 noWrap>4. The House at Pooh Corner (1923)<o></o>
</TD></TR></TBODY></TABLE><TABLE style="MARGIN: auto 6.75pt; WIDTH: 188.7pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: page; mso-table-left: 276.6pt; mso-table-top: 3.9pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=252 align=left><TBODY><TR style="HEIGHT: 14.6pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 14.6pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>The House-Warming <o></o>
</TD></TR><TR style="HEIGHT: 15.4pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 15.4pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>The Rape of the Sherlock <o></o>
</TD></TR><TR style="HEIGHT: 14.6pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 14.6pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>cold day <o></o>
</TD></TR><TR style="HEIGHT: 13.8pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>When We Were Very Young <o></o>
</TD></TR><TR style="HEIGHT: 13.8pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>Winnie-the-Pooh <o></o>
</TD></TR><TR style="HEIGHT: 13.8pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>Now We Are Six <o></o>
</TD></TR><TR style="HEIGHT: 13.8pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 188.7pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.8pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=252 noWrap>The House at Pooh Corner <o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>

 

[AlphaFrog]

This uses the .Replace method to find and replace items in each text string in column A.

Sub Clean_Up()
    
    Dim i As Variant
    
    [COLOR="Green"]'Remove leading numbers e.g. 1.  2.  10.  20.[/COLOR]
    For i = 20 To 1 Step -1
        Columns("A:A").Replace What:=Format(i, "0. "), Replacement:="", LookAt:=xlPart, _
                               SearchOrder:=xlByRows, MatchCase:=False
    Next i
    
[COLOR="Green"]    'Remove other items: "aka" and (yyyy)[/COLOR]
    For Each i In Array("aka ", "(*")
        Columns("A:A").Replace What:=i, Replacement:="", LookAt:=xlPart, _
                               SearchOrder:=xlByRows, MatchCase:=False
    Next i
    
End Sub

 

[EDUCATED MONKEY]

Thank you, for the code it looks as thou you have everything covered, I assume the list of item to be checked can be increase as required also the location to any column as required and the length of the string. If I understand this correct it start at the right end of the string and works back, evidently there is a reason or advantage to do it that way <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[EDUCATED MONKEY]

great that works fine, it just leaves me with the need to trim as there are some with leading spaces, i was going to add the to the list but realised that it would remove spaces between words as well is their away to call trim from the sub once it completes?