-1

I'm exporting a list from SharePoint online that exports lookup columns and their ID which is impossible to change.

On Export, this is what it looks like:

enter image description here

Is there a way that I can write an Excel function to delete specific characters combinations? I would like something similar to this ;#(Number between 1-2000)# and ;#(Number between 1-2000) to be deleted.

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
  • Is the string to be deleted always at the end of string starting with `;`? – urdearboy Jan 09 '19 at 17:25
  • Sadly it's not, I wanted to do ;#* but there are instances where there are users behind the numbers such as Jessica#;171;Joe;#172 –  Jan 09 '19 at 17:27

1 Answers1

0

Use the function AlphaOnly to remove all non character instances. Anything that is not [a-z, A-Z] will be scrubbed from string.

The macro Alpha aims to loop through some predetermined range and will apply the value generated by the formula. The range to-be-looped in example is A1:A10 and will need to be adjusted accordingly.

The function needs to be pasted in a Module to run. 

Option Explicit

Function AlphaOnly(strSource As String) As String

Dim i As Integer
Dim strResult As String

For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))
        Case 65 To 90, 97 To 122
            strResult = strResult & Mid(strSource, i, 1)
    End Select
Next

AlphaOnly = strResult

End Function


Sub Alpha()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")  '<-- Update
Dim i As Range

For Each i In ws.Range("A1:A10") '<-- Update
    i = AlphaOnly(i.Text)
Next i

End Sub

Function Source which has been slightly modified to fit OP needs.

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • @BigBen good catch! That means you will also need to keep the `.` (dot) for the email string. – urdearboy Jan 09 '19 at 17:49
  • OP, any characters that you want to **not** be removed can be added to the `Select Case` line. Simply look up the `CHR` code, and add it to that line as comma seperated values. The two ranges now are looking for lower case alpha and the next is looking for uper case alpha – urdearboy Jan 09 '19 at 17:50
  • Do you paste both of the codes within the same module? –  Jan 09 '19 at 18:01
  • Yup. The function needs to be in a `Module` code space. The macro has flexibility. If this is the only code you are running, putting them in the same place seems to be ideal. – urdearboy Jan 09 '19 at 18:02
  • Totally got the macro to work! Now I need to find the codes for spaces, commas, etc. Thanks so much for the help! –  Jan 09 '19 at 18:29
  • 1
    @SaintLouisEvents [here](http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type-Functions/SelectedCharactersfromVBAsCharacterCodeSet.htm) is a list of common CHR (ASCII) codes. The actual list is much longer - but I think the basic ones will work for you. – urdearboy Jan 09 '19 at 18:30
  • You are awesome! Thank you sir. –  Jan 09 '19 at 18:31
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/186427/discussion-between-saintlouis-events-and-urdearboy). –  Jan 09 '19 at 19:32