Removing everything from a cell that is not 8 characters long

[mindbender]

Is this possible to remove anything not 8 characters long from a cell in a column.

for example Column H has 4 rows (there is data in columns A-G as well but I am only looking at column H to make this change):

1. NBS CM XTEND 14260 FXC041465 FXC041466, FXC041774

2. NBS "INSTALL" 9HB915543 ...+51

3. RET SS PBB009144

4. RET CONV BG0960335 Is there something I can do so that once updated the columns look like this: 1. FXC041465 FXC041466 FXC041774 2. 9HB915543 3. PBB009144 4. BG0960335 Basically just the data with 8 characters separated by a single space. Any insight would be greatly appreciated <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[Fluff]

How about

Sub mindbender()
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   
   For Each Cl In Range("H1", Range("H" & Rows.Count).End(xlUp))
      Sp = Split(Cl)
      Cl.Value = ""
      For i = 0 To UBound(Sp)
         If Len(Sp(i)) = 9 Then Cl.Value = Cl.Value & " " & Sp(i)
      Next i
      Cl.Value = Trim(Cl.Value)
   Next Cl
End Sub

This works on 9 characters, not 8 as that is what your data is, but it will also include "INSTALL"

 

[mindbender]

How about

Sub mindbender()
   Dim Cl As Range
   Dim Sp As Variant
   Dim i As Long
   
   For Each Cl In Range("H1", Range("H" & Rows.Count).End(xlUp))
      Sp = Split(Cl)
      Cl.Value = ""
      For i = 0 To UBound(Sp)
         If Len(Sp(i)) = 9 Then Cl.Value = Cl.Value & " " & Sp(i)
      Next i
      Cl.Value = Trim(Cl.Value)
   Next Cl
End Sub

This works on 9 characters, not 8 as that is what your data is, but it will also include "INSTALL"

That is awesome - Thank you very much

 

[Fluff]

You're welcome & thanks for the feedback