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

 

[platonic567]

Hi,

Assuming all your data is in Column A, copy the below formula in B1 and drag all the way down

=IF(LEN(A1)=63,REPLACE(A1,11,3," "),A1)

 

[DRILL]

Hi,
Many thanks for the quick response.
I will certainly try your approach on my data set which can be +5000 lines long.
I manipulate the data afterwards using VBA code so I was hoping to find a VBA subroutine I could call on each line prior to performing my other VBA data manipulation.
Thanks again,
DRILL

 

[platonic567]

Sorry can't help you there, don't know coding at all

 

[DRILL]

Hi Again,
The length of the data set changes so the relative position of the I and O characters I want to remove do not remain the same.
If I understand your option correctly is relies on the characters to be changed remaining in the same relative position in the string.

What does remain the same is that the I or O character (if present) will always be between the 3rd and 4th space.
Do you have any suggestions for a formula or function that I could use with this in mind.

Many thanks again,
DRILL

8 2 4800.8 I 115000.0 39421.6 D O 2.917 I 4.850 I 2.998 C 2.706
134 2 4801.0 I 115000.0 39422.3 D O 2.917 I 4.850 O 2.998 C 2.706

 

[XOR LX]

Hi,

Perhaps:

=TRIM(REPLACE(A1,MIN(SEARCH({"O","I"},A1&"OI")),1,""))

Regards

 

[DRILL]

Hi,

Perhaps:

=TRIM(REPLACE(A1,MIN(SEARCH({"O","I"},A1&"OI")),1,""))

Regards

Hi,
Many thanks for the response.
Your solution works perfectly!

Unedited; 3 2 4461.1 O 115000.0 38214.3 D O 3.009 I 5.220 O 3.209 C 2.577 Edited: 3 2 4461.1 115000.0 38214.3 D O 3.009 I 5.220 O 3.209 C 2.577 When I copy your formula down beside my data the cell referenced to changes in the formula but it continues to actually refer to the first cell I created the code by. Is there any way I can get it to refer to the cell to the left as it is copied down. Also, do you think I could change your equation into a function and then call it from VBA code. Many thanks, DRILL

<tbody>
</tbody>

 

[XOR LX]

"When I copy your formula down beside my data the cell referenced to changes in the formula but it continues to actually refer to the first cell I created the code by."

Sorry - not sure what you mean here?

"Also, do you think I could change your equation into a function and then call it from <ACRONYM title="visual basic for applications">VBA</ACRONYM> code."

Do you mean you want this as a UDF?

Regards

 

[DRILL]

Hi again,
I just closed the file and reopened it and found the copied down formulas now refer to the cells to the left.
I am not sure how your formula works but I believe it may include an array. I must admit I have tried to understand arrays but I have never got to grips with them!
Thanks again and any further thoughts or enlightenment would be gratefully received. Particularly if you can see any way of incorporating this into a VBA subroutine (otherwise it does give me an excellent workaround/solution for pre-processing the data)
DRILL

 

[XOR LX]

Just something like this?

Sub ReplaceO_I()

Dim LRow As Long
LRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B1").Formula = "=TRIM(REPLACE(A1,MIN(SEARCH({""O"",""I""},A1&""OI"")),1,""""))"

ActiveCell.Copy

Range("B2:B" & LRow).Select
Selection.PasteSpecial

End Sub

Regards