Replacing strings/ replacing numbers and letters

[Insert Name]

I have a data set that I am trying to edit. I have strings of numbers and characters. I have created an example below.

Raw data	Clean data
757443V	757443V five RED
754520V#XY	754520V XY
267773AFL	267773A FL five RED
619979	619979 five RED
MXQAGJ	MXQAGJ one blue

I want to be able to ask excel if some criteria is meet make these changes.


If there are 6 numbers then a single character then add "five RED" to the end of the cell.
If there are 6 numbers then a character then a # then two characters substitute x with " ".
if there are 6 numbers and three characters then put in a space after the first character and add "five RED"
if just numbers then add "five red"
if just letters then add "one blue"

In my data set I have some data that doesn't conform to these rules, that is why I would like a specific code that only edits the data the meets a specific format. My data is quite large so I wont able to make a rule that states replace "AFL" with "A FL five RED". There are many combinations that have 6 numbers then three randomly selected characters. I would not be able to write a line for each unique combination.

This data is not an exact replication, so I would like to edit my data to fit this data set. I appreciate little explanation.

[zbor]

Here, try this:

=IF(ISNUMBER(1*LEFT(A1,6)),IF(ISNUMBER(SEARCH("#",LEFT(A1,7)&" "&RIGHT(A1,MAX(0,LEN(A1)-7)))),SUBSTITUTE(LEFT(A1,7)&" "&RIGHT(A1,MAX(0,LEN(A1)-7)),"#",""),LEFT(A1,7)&" "&RIGHT(A1,MAX(0,LEN(A1)-7))&" five RED"),A1&" one blue")

[Insert Name]

Would it be possible to get this in more than one formula? If the formula leaves a blank if the string does not meet the criteria then I can look at it later.
Then the first formula would look like:

Raw data	Clean data
757443V	757443V five RED
754520V#XY	"blank"
267773AFL	"blank"
619979	"blank"
MXQAGJ	"blank"

Then I could edit each formula to fit my data better.

Raw data	Clean data
757443V	"blank"
754520V#XY	754520V XY
267773AFL	"blank"
619979	"blank"
MXQAGJ	"blank"

Something like this would leave more columns but I can clean that later.
Help is appreciated!