none
Replacing Specail Characters in a sheet RRS feed

  • Question

  • Hi

    I have a sheet which has 30 columns and a variable number of rows maybe as much as 7000.

    Within the data in the sheet I need to look for 5 special charactes (" & ' < >) and replace them with (A B C D E). I looking for a quick way to do this.

    I know I can load into an array and search through the whole array but I am concerned about how long this take - bearing in mind that this is just a small part of my application.

    Annoyingly I would not expect more that 20 occurrences of these special characters in my data so I am looking for something slick and easy.

    Any ideas please?

    thanks

    Peter 

    Tuesday, March 28, 2017 8:35 AM

Answers

  • Hi py1,

    If you do not mind writing code (VBA), it could be done easily.

    I suppose the code would be like this:
    Dim rowIdx, colIdx As Integer   ' -- or "Long" if rows/columns more than 65,536
    For rowIdx = 1 To 7000          ' -- please change 7000 according the number of Rows
        For colIdx = 1 To 30        ' -- please change 30 according the number of Columns
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, """", "A")   ' -- replace double quoto to A
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "&", "B")    ' -- replace amparsand to B
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "'", "C")
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "<", "D")
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, ">", "E")
        Next colIdx
    Next rowIdx
      note: Using Chr(34) may be more readable than using """".
               Chr(34) means double quotation.

    Regards,
    Ashidacchi

    • Edited by Ashidacchi Tuesday, March 28, 2017 10:37 AM
    • Marked as answer by py1 Tuesday, March 28, 2017 5:21 PM
    Tuesday, March 28, 2017 10:25 AM
  • Suggested book:
    "M is for Data Monkey"
    "The Excel Pro's Definitive Guide to Power Query"
    by Puls and Escobar.
    PQ aka "Get & Transform" in Excel 2013/2016.

    • Marked as answer by py1 Wednesday, March 29, 2017 7:05 AM
    Tuesday, March 28, 2017 6:49 PM

All replies

  • Hi py1,

    If you do not mind writing code (VBA), it could be done easily.

    I suppose the code would be like this:
    Dim rowIdx, colIdx As Integer   ' -- or "Long" if rows/columns more than 65,536
    For rowIdx = 1 To 7000          ' -- please change 7000 according the number of Rows
        For colIdx = 1 To 30        ' -- please change 30 according the number of Columns
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, """", "A")   ' -- replace double quoto to A
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "&", "B")    ' -- replace amparsand to B
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "'", "C")
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, "<", "D")
            Cells(rowIdx, colIdx).Value = Replace(Cells(rowIdx, colIdx).Value, ">", "E")
        Next colIdx
    Next rowIdx
      note: Using Chr(34) may be more readable than using """".
               Chr(34) means double quotation.

    Regards,
    Ashidacchi

    • Edited by Ashidacchi Tuesday, March 28, 2017 10:37 AM
    • Marked as answer by py1 Tuesday, March 28, 2017 5:21 PM
    Tuesday, March 28, 2017 10:25 AM
  • Hi

    I was aware I could it like this - I was hoping for a slicker way - perhaps none exists?

    thanks for your input anyway.

    Peter

    Tuesday, March 28, 2017 1:01 PM
  • Ashidacchi's "VBA" code is about as slick as it gets.
    But in case you are more familiar with "M" code...
    http://www.mediafire.com/file/t119f0u86k8xqfl/03_28_17a.xlsx
    http://www.mediafire.com/file/y67h79c21qor32r/03_28_17a.pdf

    Tuesday, March 28, 2017 4:11 PM
  • Herbert,

    Thanks - no I am not familiar with M code. Did not understand the attachments - can you give ma clue please, always good to learn something new.

    thanks

    Peter

    Tuesday, March 28, 2017 5:22 PM
  • Suggested book:
    "M is for Data Monkey"
    "The Excel Pro's Definitive Guide to Power Query"
    by Puls and Escobar.
    PQ aka "Get & Transform" in Excel 2013/2016.

    • Marked as answer by py1 Wednesday, March 29, 2017 7:05 AM
    Tuesday, March 28, 2017 6:49 PM
  • thanks Herbert - have ordered the book!
    Wednesday, March 29, 2017 7:54 AM