Con macros como separo números y texto de una celda

Según veo (y creo) no hace falta una macro para lograr lo que quieres... aunque hay que implementar un proceso de tres pasos:
1. Si te fijas el código alfanumérico esta separado por un espacio del texto restante
2. Si usamos una función SUSTITUIR para reemplazar ese primer espacio (el texto puede contener otros espacios) por un carácter indicador (ejemplo ::), y copiamos y pegamos los valores obtenidos con el uso de la función
3. Podemos usar el procedimiento TEXTO EN COLUMNAS de Excel (usando como carácter separador el indicador que usamos en la fórmula (::)) para separar las cadenas de texto en el punto que se requiere.
...

Una solución alterna y efectiva para tu caso es: si loscódigosson separados correctamente y suponemos que la la celda A1 contiene la cadena sin modificación y la celda B1 contiene el código, entonces en la celda C1 introduce esta fórmula para obtener el texto

=SUSTITUIR(A1,B1&" ","")

.....
Sin embargo, y como no conozco todo el proceso que estas ejecutando te dejo los códigos que insertan las fórmulas en las celdas contiguas (a la derecha de la actual) y que dan como resultado lo que necesitas

Sub SepararCodigoFormula()
''Creada por FSerrano en 120322
''para perdido32 en TodoExpertos.com
''separa una cadena en dos partes, tomando como punto base el primer espacio que encuentra en la cadena
valor = "SUSTITUIR(" & ActiveCell.Address(False, False) & ","" "",""::"", 1)"
ActiveCell.Offset(0, 1).FormulaLocal = "=med(" & valor & ",1,encontrar(""::""," & valor & ",1)-1)"
ActiveCell.Offset(0, 2).FormulaLocal = "=med(" & valor & ",encontrar(""::""," & valor & ",1)+2,LARGO(" & valor & ")-ENCONTRAR(""::""," & valor & ",1)+2)"
End Sub

Pruébalo y me cuentas como te va.