How to edit string to remove a single character between the 3rd and 4th space

[DRILL]

Hi,
I have a long list of varied data.
I have some data where an O or I character may or may not appear.
If it does appear is always the one between the 3rd and 4th space that is of interest for me.
If it does appear I want the O or I to be deleted leaving just a single space.
O and I can appear at other positions in the string so I cannot use a blanket replace method.

I am moving up through the data line by line and would be grateful if anybody could suggest a VBA routine to do this.
Some example data is shown below. The characters in bold are the ones I would like to delete leaving just a single space at the same point.

Speed is not critical for me.

Many thanks for any suggestions.
DRILL

String
Section
MD
(ft)
Yield Strength
(psi)
VME Stress
(psi)
Absolute Safety Factors
Triaxial Burst Collapse Axial
1 1 4431.1 115000.0 48045.5 D 2.394 100+ 23.676 2.571
2 1 4460.9 115000.0 47964.7 D 2.398 100+ 23.541 2.577
3 2 4461.1 O 115000.0 38214.3 D O 3.009 I 5.220 O 3.209 C 2.577
4 2 4560.8 O 115000.0 38560.8 D O 2.982 I 5.105 O 3.144 C 2.613
5 2 4561.0 O 115000.0 38561.5 D O 2.982 I 5.105 O 3.144 C 2.614
6 2 4587.8 O 115000.0 38655.6 D O 2.975 I 5.075 O 3.127 C 2.624
7 2 4588.0 I 115000.0 38656.4 D O 2.975 I 5.075 O 3.127 C 2.624
8 2 4800.8 I 115000.0 39421.6 D O 2.917 I 4.850 I 2.998 C 2.706
9 2 4801.0 I 115000.0 39422.3 D O 2.917 I 4.850 O 2.998 C 2.706

 

[DRILL]

Many thanks,
I tried your code and it worked really well. I can now run this subroutine to 'clean' the data and then copy and paste special the data in column B as values and then delete column A. I can then run my VBA code on the cleaned data.
One last thing.
As you can see below this approach has also taken out the I and O in some of the heading text (Absolute is now Ab****e). I do refer to some of these headings in my VBA code. I can get around this by using REPLACE as the headings are consistent for the data set.
So this a workable solution and many thanks for your time and expertise.

One last challenge. Is there a VBA subroutine you can think of which would go across each single cell string and then remove an I or O leaving only one space if the I or O should occur between the 3rd and 4th space. The position relative to the 3rd and 4th space is always the same in the dataset. This subroutine could be run on each cell. Start at the bottom of the data set. Run the subroutine on the last cell. Move up one cell and run the subroutine again, and so on.

String Strng
Section Secton
MD MD
(ft) (ft)
Yield Strength Yeld Strength
(psi) (ps)
VME Stress VME Stress
(psi) (ps)
Absolute Safety Factors Ab****e Safety Factors
Triaxial Burst Collapse Axial Traxial Burst Collapse Axial
1 1 4431.1 115000.0 48045.5 D 2.394 100+ 23.676 2.571 1 1 4431.1 115000.0 48045.5 D 2.394 100+ 23.676 2.571
2 1 4460.9 115000.0 47964.7 D 2.398 100+ 23.541 2.577 2 1 4460.9 115000.0 47964.7 D 2.398 100+ 23.541 2.577
3 2 4461.1 O 115000.0 38214.3 D O 3.009 I 5.220 O 3.209 C 2.577 3 2 4461.1 115000.0 38214.3 D O 3.009 I 5.220 O 3.209 C 2.577
4 2 4560.8 O 115000.0 38560.8 D O 2.982 I 5.105 O 3.144 C 2.613 4 2 4560.8 115000.0 38560.8 D O 2.982 I 5.105 O 3.144 C 2.613
5 2 4560.8 O 115000.0 38560.8 D O 2.982 I 5.105 O 3.144 C 2.613 5 2 4560.8 115000.0 38560.8 D O 2.982 I 5.105 O 3.144 C 2.613

 

[XOR LX]

Sorry - not sure what you mean again.

Are you saying you simply want to be able to run this code on more than one column's worth of data?

If so, have you tried simply amending the range in the code I provided?

Regards

 

[DRILL]

Sorry if I have made this confusing.
I only have one column of +5000 string string data values (all in column A).
I have some rudimentary VBA code I use to manipulate this data after I have concatenated it.

The data now has this possible occurrences of O and I in the data set.
I can handle this in most of the string but I cannot handle it if it occurs between the 3rd and 4th space in the string (this is why I need to delete this occurrence).
Your solution works perfectly well and I end up with a cleaned data set in column B which I can then paste special values to get data again which I can run my VBA code on.
The only downside with this approach is that some of the heading text also gets the I and O removed.

Thats why I was wondering if there was a more surgical approach looking at the text between 3rd and 4th space in the string and then removing it if it is an O or I - to just leave a single space.
A subroutine like this could be run on each string working from the base of the data set to the top. Possibly not an elegant solution by coding standards but it should do the job and not affect the titles.
Cheers,
DRILL

 

[XOR LX]

But why can't you just set the applicable range for the code to exclude the row(s) containing your headers?

Regards

 

[DRILL]

The data comes from pdf A$ report print outs which I cut and paste into Excel - the headers appear intermittently and irregularly all the way down the data set.
I will use the solution you gave me and if necessary I can use REPLACE to correct any changes to the headers as it will only affect text with an I or O in the string.
In the meantime many thanks for all of your help it is much appreciated.
DRILL

 

[XOR LX]

I see. Ok, well let me know if your temporary solution isn't working and I'll have a think about how the code could account for these cases.

Regards