IF Function

[kenadams378]

Hi,

I have some variable data that i need to format depending on the length and content of the data.

For example;

If the data contains an A i need to trim that out and add in a space before the number.
If the data is only 3 characters i need to add in a space before the number
If the data is 5 characters then leave as it is.

I have hardcoded the data in the exmaple, just not sure of how to put the formula together?

[martindwilson]

is that A always at the end?

[vamosj]

Just as Martin said, it would will be better able to assist if we can understand the general formatting of the Data..

Questions I have,

1) Is the A always at the end
2) If the A is dropped will the number always be the last character of the data?

[AlKey]

Try this

=SUBSTITUTE(SUBSTITUTE(A2,"a",""),"A","")

[kenadams378]

Yes A always at the end & Yes number will always be last character,

[vamosj]

Okay, give this one a shot... Put it in cell C2 and copy & paste down the chart.

Please Login or Register  to view this content.

[newdoverman]

If your data is as your example then, this should work.

Please Login or Register  to view this content.