Reorganize Existing Datum With Text Formula

[jbesr1230]

I have been given many existing data coded as follows "EXMPL1A 05/25/2018 2,150.00 D" (without quote marks) each datum is this format: a descriptor, space, date in mm/dd/yyyy, space, Amount numeric with 2 places to the right of the decimal, space, alpha-character.

I need to convert the example above to this: EXMPL1A180525D2150. This is: Descriptor, yymmdd, alpha-character, numeric (without the comma and the .00). For clarification (Descriptor) EXMPL1A - (date - yymmdd); 180525 - (alpha-character) D; (amount-if cents is .00 exclude the .00, if cents is ###.#0 exclude the final 0) so 2150.

More examples:

ABCD 06/15/2018 93.50 G - CONVERT TO: ABCD180615G93.5
PVHW3Q 07/12/2018 1450.00 I - CONVERT TO: PVHW3Q180712I1450
THQR 08/17/2019 65.25 K - CONVERT TO: THQR 190817K65.25

Thank you.

JB

 

[Peter_SSs]

Many thanks to all of you - Erik, Aladin, Peter, Nishant94 !!. On my system, all your formulas worked.

You're welcome. That is all good news, you have lots of choice.