0

I have strings like these -

CRS|R|S||3.0|25|W||U||||0||ECN|211|MACROECONOMIC PRINCIPLES
CRS|R|S||3.0|25|F||U||||0||CIS|105|SURVEY COMPUTER INFO SYSTEMS
CRS|R|S||3.0|25|A||U||||12||CSR|207|AUTOMOBILE POLICY ADJUSTMENT

The format of these will always be like this. I want to remove any numbers just before ECN, CIS or CSR above. So after processing, my output should look like this -

CRS|R|S||3.0|25|W||U||||||ECN|211|MACROECONOMIC PRINCIPLES
CRS|R|S||3.0|25|F||U||||||CIS|105|SURVEY COMPUTER INFO SYSTEMS
CRS|R|S||3.0|25|A||U||||||CSR|207|AUTOMOBILE POLICY ADJUSTMENT

0 and 12 are removed from the strings above.

Please note - ECN, CIS or CSR are just for example. It can be anything. I want to remove the numbers before these 3 letters.

Please help me out !

Kedar Joshi
  • 1,182
  • 1
  • 20
  • 27

1 Answers1

2

Try Split on the |, emptying the value at the appropriate position, and then Join back together with the |.

Option Explicit

Dim input
Dim lines, ub, i, lineParts
Dim output

input = "CRS|R|S||3.0|25|W||U||||0||ECN|211|MACROECONOMIC PRINCIPLES" & vbCrLf & _
    "CRS|R|S||3.0|25|F||U||||0||CIS|105|SURVEY COMPUTER INFO SYSTEMS" & vbCrLf & _
    "CRS|R|S||3.0|25|A||U||||12||CSR|207|AUTOMOBILE POLICY ADJUSTMENT"

lines = Split(input, vbCrLf)
ub = UBound(lines)
For i = 0 To ub
    lineParts = Split(lines(i), "|")
    lineParts(12) = ""
    lines(i) = Join(lineParts, "|")
Next

output = Join(lines, vbCrLf)
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Thanks for you reply. Could you please give me some more idea? I am completely new to vbs ! I am already doing Split on |. I can even find the appropriate location. How can I join back? – Kedar Joshi Aug 27 '13 at 17:57
  • My problem is that I am doing other operations as well. So I want to remove many of intermediate strings, modify some of the strings and then finally do the operation of replacing those numbers. How can this be done? – Kedar Joshi Aug 27 '13 at 18:04
  • @KedarJoshi I refer you to [this](http://stackoverflow.com/a/13911760/111794) answer, with some useful links for VBScript. – Zev Spitz Aug 27 '13 at 19:04