Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Insert substring at certain point (2K3)

    This questions is very similar to my question <!post=Remove a pilcrow (2K3),659507>Remove a pilcrow (2K3)<!/post> but this time in Excel.

    In column B I have 2720 or so lines of descriptive text; this set of text is connected to an Active Server Page (ASP). As an aside the ASP just sees it as a text string and formatting is not carried across.

    An example of the text could be:

    1000 is 103

    To convert so that ASP converts it correctly it must be typed in the cell as follows:

    1000 is 10<sup>3</sup>

    and it will look like this:

    1000 is 10

    The trick is nowhow to get the <sup> and </sup> to insert. ...any ideas?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert substring at certain point (2K3)

    Here is a macro:
    <code>
    Sub InsertSup()
    Dim r As Long
    Dim n As Long
    Dim strVal As String
    Dim p As Integer
    Dim a As Long
    Dim b As String
    Dim m As Integer
    Dim i As Integer
    Dim c As Integer
    Dim d As Integer
    ' Last used row
    n = Range("B65536").End(xlUp).Row
    On Error GoTo ErrHandler
    ' Loop throug rows - adjust start number as needed
    For r = 1 To n
    ' Cell value
    strVal = Range("B" & r).Value
    ' Position of " in "
    p = InStr(strVal, " is ")
    ' Process only if found
    If p > 0 Then
    ' Value of left hand part
    a = CLng(Left(strVal, p - 1))
    ' Right hand part
    b = Mid(strVal, p + 4)
    ' Length of right hand part
    m = Len([img]/forums/images/smilies/cool.gif[/img]
    ' Try to split
    For i = 2 To b - 1
    ' Base
    c = CInt(Left(b, i - 1))
    ' Exponent
    d = CInt(Mid(b, i))
    ' Do we have the correct power?
    If a = c ^ d Then
    ' Insert tags
    strVal = Left(strVal, p + i + 2) & "<sup>" & _
    Mid(strVal, p + i + 3) & "</sup>"
    ' Write back to column C (change this to B for production)
    Range("C" & r) = strVal
    ' Done with this cell
    Exit For
    End If
    Next i
    End If
    NextRow:
    Next r
    Exit Sub
    ErrHandler:
    If Err = 13 Then
    ' Not a power - skip
    Resume NextRow
    Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </code>
    Comments:
    1) If you want to start in row 2, change

    For r = 1 To n

    to

    For r = 2 To n

    2) This code version the results to column C. When you've tested the code, you may want to replace the original values. Change

    Range("C" & r) = strVal

    to

    Range("B" & r) = strVal

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert substring at certain point (2K3)

    Afterthought: since most exponents will probably be single digits, it is more efficient to use

    For i = b - 1 To 2 Step - 1

    instead of

    For i = 2 To b - 1

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert substring at certain point (2K3)

    Bleeding heck, thanks Hans that is some neat piece of code and will certainly fit some of the examples I have in my list

    Now to do it for:

    121 is the only square known of the form 1 + p + p2 + p3 + p4, where p is prime. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    PS <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Don't worry, only joking
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •