# Excel fill data as per criteria

Hello Experts,
I have attached an Excel, which contains Data in the first column as raw data.
The data is starting with some identifiers as: Alphabet, Roman Number, Number, Small Alphabet & Article No.

I want to separate the data in different column as per the starting value of the data, I have made columns to demonstrate what exactly is the end result.

The last column is called Check, which actually should provide the result if the values in column A are contained somewhere from column C to column G.

A
Sample.xlsx
Subodh Tiwari (Neeraj)

Try this.....

In A3
``=IF(AND(ISNUMBER(MATCH(CODE(LEFT(\$A3,1)),{73,86,88,76},0)),LEFT(C\$1,5)="Roman"),\$A3,IF(AND(CODE(LEFT(\$A3,1))>=65,CODE(LEFT(\$A3,1))<=90,MID(\$A3,2,1)=".",C\$1="Alphabet"),\$A3,IF(AND(ISNUMBER(LEFT(\$A3,1)+0),C\$1="Number"),\$A3,IF(AND(CODE(LEFT(\$A3,1))>=97,CODE(LEFT(\$A3,1))<=122,LEFT(C\$1,5)="Small"),\$A3,IF(AND(LEFT(\$A3,3)="Art",C\$1="Article No."),\$A3,"")))))``
And then copy across to column G and down the rows.

For details refer to the attached workbook.
Sample.xlsx
[ fanpages ]

^ Check cells [C4] & [D5].  There is a mismatch in the requirements.

The prefix "I." is being detected as being in the "Alphabet" column & the "Roman (Numeral)" column; as you would expect.

Should the "Roman" check be in advance of everything else &, hence, is any valid Roman Numeral symbol (I, V, X, L, C, D, &/or M) invalid within the "Alphabet" column?

Similarly, cell [D8] shows "II." as a valid "Roman" entry.

Hence, should any valid Roman Numeral (i.e. any numeric quantity in Roman Numeral form) be just in the "Roman" column?

That is, if the prefix to a value in column [A] contains a string of Roman Numeral symbols, but is not actually a valid Roman Numeral, does that mean that the column is "Alphabet" (only)?

Looks Great!!!
Give me a day to test as I have to test.

Thanks, A
A: Please see my reservations within the comment immediately before your last comment (above).
Subodh Tiwari (Neeraj)

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

sktneer,
Looks Great!

@fanpages,
Thanks!
Thanks. You need to test it to see if you get the desired output with all the possible input in col. A.
No problem, guys :)

Gentlemen,
I have another stumble...
I have the export for the same stuff, but since data is from a text file it's spread across various columns...
Is there a formula to say, look from column B to column F & if you find something cut it & put in column A?

A
Well that's completely a different question altogether.

Would be better if you mark this question as Solved by accepting one of the solutions provided as your original question has been already answered here and open another question with your new requirement along with a sample workbook. This way your new question will get more attention from the experts here.