Macro para transformar datos en excel

He hecho esta función que creo hace lo que quieres:

Function MiFormato(s As String) As String
Dim i As Integer
Dim aux(2) As String
'Ultimo caracter
aux(2) = Right(s, 1)
If aux(2) Like ("[0-9]") Then ' es numero
 aux(2) = ""
Else
 s = Left(s, Len(s) - 1)
End If
i = Len(s)
aux(1) = ""
While (Mid(s, i, 1) Like ("[0-9]"))
 i = i - 1
Wend
aux(1) = Right(s, Len(s) - i)
i = 1
aux(0) = ""
While Mid(s, i, 1) Like ("[A-Za-z]")
 i = i + 1
Wend
aux(0) = Left(s, i - 1)
MiFormato = aux(0) & " - " & aux(1) & aux(2)
End Function

La incluyes en un módulo y luego la escribes en una celda por ejemplo asi:

=MiFormato(A1)

La función aparece en las funciones definidas por el usuario una vez definida.

Tal vez lo mas sencillo es hacer referencia a una celda de otra hoja y arrastrar la función por un rango similar al de la hoja origen. Así tendrías en una hoja tus datos origiinales y en la otra los corregidos.

Si por cualquier causa necesitas los valores, copia y con pegado especial, pegas los valores (sin formula) donde quieras.

Si prefieres una macro que transforme los valores originales simplemente crea una macro que recorra todas las celdas del rango donde están los valores, llamando a la función sobre ellas y cambiando el valor original por el resultado.

Hola! gracias por tu rápida respuesta! esta muy bien tu función pero solo me separa con guion, no me le agrega los 0! te coloco algunos ejemplos para que entiendas mejor lo que quiero:

BC4 = BC-0004

VBA 153 = VBA-0153

VG32ST = VG-0032ST

JJ2000 = JJ-2000

Las letras del principio son maximo 3, las que pueden estar despues de los numeros pueden ser 1 o 2 maximo y los numeros son maximo de 4 digitos, pueden estar separados por espacio, pegados, o en algunos casos separados por un %. Ayudame con esto por favor! Y GRACIAS

Aquí tienes las correcciones.

Function MiFormato(s As String) As String
Dim i As Integer
Dim j As Integer
Dim aux(2) As String
'Ultimos 2 caracter
aux(2) = ""
For j = 1 To 2
 If Right(s, 1) Like ("[A-Za-z]") Then ' es letra
 aux(2) = Right(s, 1) & aux(2)
 s = Left(s, Len(s) - 1)
 End If
Next j
'Numeros del medio
i = Len(s)
aux(1) = ""
While (Mid(s, i, 1) Like ("[0-9]"))
 i = i - 1
Wend
aux(1) = Right(s, Len(s) - i)
aux(1) = Format(aux(1), "0000")
'cabecera
i = 1
aux(0) = ""
While Mid(s, i, 1) Like ("[A-Za-z]")
 i = i + 1
Wend
aux(0) = Left(s, i - 1)
MiFormato = aux(0) & " - " & aux(1) & aux(2)
End Function

Es posible que solo jugando con la función Format se pueda hacer también, aunque mezclar formato de número y letras al tiempo me parece engorroso y con menos control de lo que pasa.

Hermano funciona perfectamente era justo lo que necesitaba! me gustaría aprender mas de esas funciones soy nuevo con los macros pero quisiera aprender de eso y de las userform si tienes alguna pagina que me recomiendes donde haya un manual para ver los comandos o si tienes algo que me puedas enviar te lo agradeceria mucho. Muchas gracias por tu ayuda una vez mas y la calificacion es excelente...

Para aprender de macros se necesita saber o aprender de programación en general, incluso de cualquier otro lenguaje. En mi opinión es mejor partir de un lenguaje estructurado (para aprender a pensar y hacer las cosas con lógica estructurada). Empezar por programación orientada a objetos, sin más, no es lo más adecuado en mi opinión, pero es solo una opinión.

En internet hay mucha información que puedes encontrar en forma de tutoriales, basta con poner "tutorial VBA" en google.

Otra forma que te puede servir cuando tienes ya unos fundamentos es grabar la macro, y ver que se ha guardado de la grabación. No siempre es lo mejor, de hecho no suele serlo en cuanto la cosa se complica, pero sí te dá pistas de qué hacer.

Por último, recuerda, que siempre suele haber varias formas de llegar a la solución y no suele haber una mejor que las otras. Va mucho en gustos y las necesidades de cada uno. No es lo mismo hacer un prototipo rápido, que hacerlo para enseñar (como suelo intentar en mis macros aunque no sea lo mas eficiente) y por claridad, que buscar solo eficiencia.