none
Find and Replace in Excel Sheet

    Question

  • hi

    i had a requirement to find and replace the "~" only at the positions 9th 18th 27th........ in a excel sheet single cell. i had tried VBA and excel formula but not worked.

    example 

    12345678~12345678~12345678~12345678~

    12345678 12345678 12345678 12345678 

    Can you please provide  a solution either using VBA or Excel Formula.

    Thank you in Advance.



    - Ram


    • Edited by David Billa Thursday, January 03, 2013 7:45 AM
    Thursday, January 03, 2013 7:44 AM

Answers

  • Alternatively, and quite rudimentary....

    =LEFT(A1,8)&" "&MID(A1,10,8)&" "&MID(A1,19,8)&" "&MID(A1,28,8)

    Cheers,
    Dave

    Thursday, January 03, 2013 1:27 PM
  • hi davelang

    Thank you 

    its working fine 

    one suggestion from myside why can't you try for VBA Code to automate the same process


    - Ram

    • Marked as answer by David Billa Thursday, January 03, 2013 4:26 PM
    Thursday, January 03, 2013 1:39 PM

All replies

  • Option Explicit
    Option Compare Text

    Public Sub SearchAndReplace()

        ' Range of cells to identify JobNo
        Dim rngScan As Range
       
        ' Cell looping index
        Dim iCellIndex As Integer
       
        ' Current Cell Value
        Dim strSource As String
       
        ' Modified Cell Value
        Dim strDest As String
       
        ' Select cell range
        Set rngScan = ThisWorkbook.ActiveSheet.Range("A1:A1000")
           
        ' Iterate through each cell in the given range of the current Sheet
        For iCellIndex = 1 To rngScan.Cells.Count
           
            ' Target Position 9, 18, 27,..
            If iCellIndex Mod 9 = 0 Then
           
                ' retrieve each row cell contents in column 1
                strSource = rngScan.Cells(iCellIndex, 1).Text
                   
                If InStr(strSource, "~") > 0 Then
                    strDest = Replace(strSource, "~", " ")
                    rngScan.Cells(iCellIndex, 1) = strDest
                End If
               
             End If
            
        Next iCellIndex
         
    End Sub

     


    It all Happenz Sendil

    • Proposed as answer by sendilg Wednesday, February 20, 2013 9:19 AM
    Thursday, January 03, 2013 8:54 AM
  • Hi Sendhil

    Thank you for your Effort in posting this .

    i had tried this code it not worked eventhough i changed If InStr(strSource, "~") > 0 Then to If InStr(strSource, "~") = 0 Then

    anything to be done from my side on the code that you gave?

    Thank you in advance


    - Ram

    Thursday, January 03, 2013 9:45 AM
  • Do you need replacement in multiple of 9....

    I don't see any ~ in any other position except 9,18.

    It may be that you have not given.Or may be it is always positioned at multiple of 9.

    If it is always then use below:

    =SUBSTITUTE(A1,"~"," ")

    In A1 the text is present.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, January 03, 2013 10:49 AM
    Answerer
  • Alternatively, and quite rudimentary....

    =LEFT(A1,8)&" "&MID(A1,10,8)&" "&MID(A1,19,8)&" "&MID(A1,28,8)

    Cheers,
    Dave

    Thursday, January 03, 2013 1:27 PM
  • hi davelang

    Thank you 

    its working fine 

    one suggestion from myside why can't you try for VBA Code to automate the same process


    - Ram

    • Marked as answer by David Billa Thursday, January 03, 2013 4:26 PM
    Thursday, January 03, 2013 1:39 PM
  • No worries, simple VBA function...

    Public Function sOutput(sInput As String) As String
    Dim i As Long, s As String
    For i = 1 To VBA.Len(sInput)
        Let s = VBA.Mid(sInput, i, 1)
        Let sOutput = sOutput & VBA.IIf(i Mod 9 = 0, VBA.Replace(s, "~", " "), s)
    Next
    End Function

    Thursday, January 03, 2013 2:36 PM
  • The code i have give will look for the cells in the Range A1:A1000... U can change the range of ur choice.

    Actually i thought of target cells to be in multiples of 9..

    But its only the position of "~" within the string itselft.. Find the modified code below

    Option Explicit
    Option Compare Text

    Public Sub SearchAndReplace()

        ' Range of cells to identify JobNo
        Dim rngScan As Range
       
        ' Cell looping index
        Dim iCellIndex As Integer
       
        ' Current Cell Value
        Dim strSource As String
       
        ' Modified Cell Value
        Dim strDest As String
       
        ' Select cell range
        Set rngScan = ThisWorkbook.ActiveSheet.Range("A1:A1000")
           
        ' Iterate through each cell in the given range of the current Sheet
        For iCellIndex = 1 To rngScan.Cells.Count
           
     ' retrieve each row cell contents in column 1
            strSource = rngScan.Cells(iCellIndex, 1).Text
                   
            If InStr(strSource, "~") > 0 Then
               strDest = Replace(strSource, "~", " ")
               rngScan.Cells(iCellIndex, 1) = strDest
            End If
                 
        Next iCellIndex
         
    End Sub


    It all Happenz Sendil

    Friday, January 04, 2013 4:03 AM
  • Hi

    Thank you for the modified code that you sent. but my requirement is the same as what i mentioned forum to replace the character  at 9th 18th 27th ..................places


    - Ram

    Friday, January 04, 2013 6:17 AM
  • dim tmp_str_a as string

    tmp_str_a="~12345678~12345678~12345678~12345678~"

    tmp_str_a=Replace(tmp_str_a, "~", " ")

    tmp_str_a=trim(tmp_str_a)

    Sunday, June 23, 2013 2:22 PM