Re: Semicolon problem

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 09/30/04 

Date: Thu, 30 Sep 2004 10:55:20 -0400

This allows you to make the call. Otherwise, you would need a list of place
names that the code could compare against to make its own decision.

Sub AAA()
Dim cell As Range
Dim sStr As String
For Each cell In Selection
  sStr = cell.Value
  If Len(sStr) - Len(Application.Substitute(sStr, ";", "")) > 3 Then
    iloc = InStr(1, sStr, ";", vbTextCompare)
    iloc = InStr(iloc + 1, sStr, ";", vbTextCompare)
    sStr1 = Left(sStr, iloc - 1) & " " & Right(sStr, Len(sStr) - iloc)
    iloc = InStr(iloc + 1, sStr, ";", vbTextCompare)
    sStr2 = Left(sStr, iloc - 1) & " " & Right(sStr, Len(sStr) - iloc)

    msg = sStr & vbNewLine & vbNewLine & _
      "1. " & sStr1 & vbNewLine & _
      "2. " & sStr2 & vbNewLine & vbNewLine & _
      "Yes for 1, No for 2, Cancel to skip:"
    ans = MsgBox(msg, vbYesNoCancel)
    If ans = vbYes Then
      cell.Value = sStr1
    ElseIf ans = vbNo Then
      cell.Value = sStr2
    End If
  End If
Next
End Sub 

-- 
Regards,
Tom Ogilvy
"MavRiK" <MavRiK.1degen@excelforum-nospam.com> wrote in message
news:MavRiK.1degen@excelforum-nospam.com...
>
> Sorry guys, when i created the thread i wanted to make the wrong
> semicolon BOLD, but instead it added a * at both sides of the
> semicolon.Sorry.
> What i wanted to say is:
>
> imported into excel: (Semicolon where space should be)
> TFS Securities (Pty) Ltd;Sandton;Gauteng;011 465 3686
> Vector Equities (Pty) Ltd;Cape;Town;Western Cape;021 419 3992
> Watermark Securities (Pty) Ltd;Hyde;Park;Gauteng;011 325 4228
> Cazenove South Africa (Pty) Ltd;Dunkeld;West;Gauteng;011 280 7900
>
> The first one is correct but the last 3 needs changing:
> Vector Equities (Pty) Ltd;Cape Town;Western Cape;021 419 3992
> Watermark Securities (Pty) Ltd;Hyde Park;Gauteng;011 325 4228
> Cazenove South Africa (Pty) Ltd;Dunkeld;West Gauteng;011 280 7900
>
>
> OK, the semicolons are all the same no characters at the wrong
> one....so i have to go through the string and test whether the ; is at
> the right place.
> if it is then go to next cell, if not then replace with ;. I dont know
> how to test thje strings because ; are at differnt places. As seen
> above.
>
>
>
> Peter
>
>
> -- 
> MavRiK
> ------------------------------------------------------------------------
> MavRiK's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14728
> View this thread: http://www.excelforum.com/showthread.php?threadid=265047
>

Relevant Pages

  • Re: IF statement to check if it is a number or word
    ... I want my ans to be actually this... ... Since your a is a cell array you should index into it using curly braces ... When you concatenate a string, ... the second is the cell that was in the middle of your concatenation. ...
    (comp.soft-sys.matlab)
  • Re: Create macro
    ... I realized I had to make change to the After string ... Rather than editing macro below to change ... Dim ans ... Dim cell As Range ...
    (microsoft.public.excel.worksheet.functions)
  • Re: table (matrix) of strings
    ... Use a cell array. ... The contents of a cell that contains a string can be treated like any other ...
    (comp.soft-sys.matlab)
  • Re: Counting multiple values in a single cell
    ... With the string you want to find in A1 ... And if you may search for; (semicolon), this would be safer: ... I would like to be able to count the number of values in this cell. ...
    (microsoft.public.excel)
  • Re: macros or input box
    ... Look at Data validation. ... Dim as as String ... ans = InputBox("Please enter date you want to use in this format: ... > and have the data put into a cell of my choice ...
    (microsoft.public.excel.programming)