Splitting strings based on multiple rules and values

Question

I am trying to construct a SQL INSERT statement from some data in an Excel spreadsheet. Here is some sample data:

ORIGINAL       |     DESIRED OUTPUT
ND                   0, null
0.23                 0.23, null
0.45 J               0.45, 1
0.99 B               0.99, 2

So the rules would be:

• If there is a ND the ND should be converted to a 0 and the next field will be a null
• If there is just a number we just add that number as is plus a null field
• If there is a 'J' after the number the output fields should be the number and then a '1'
• If there is a 'B' after the number the output fields should be the number and then a '2'

I can create individual rules without too much trouble, but I can't seem to string them together. For example:

=IF(A1="ND","0, null",A1)

The above will take care of the first instance of 'ND', but not sure how to add in the other rules.

Answer 1

Please try:

=IF(A1="ND","0, null",IF(RIGHT(A1)="J",LEFT(A1,LEN(A1)-2)&", 1",IF(RIGHT(A1)="B",LEFT(A1,LEN(A1)-2)&", "&2,A1&", null")))