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

 

[inactiveuserps07]

JB, if your raw data were in A1, place the following in B1 to convert:

=LEFT(A1,FIND(" ",A1)-1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)-2,2)&REPLACE(MID(A1,FIND(" ",A1)+1,5),3,1,"")&RIGHT(A1,1)&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1,FIND(" ",A1)+1),10),",",""),MID(A1,FIND(".",A1),3),""),RIGHT(A1,1),""))&IF(VALUE(MID(A1,FIND(".",A1),3))>0,VALUE(MID(A1,FIND(".",A1),3)),"")

 

[inactiveuserps07]

Actually, the formula above has one glitch I just noticed. Use the following shorter formula:

=LEFT(A1,FIND(" ",A1)-1)&TEXT(MID(A1,FIND(" ",A1)+1,10),"yymmdd")&RIGHT(A1,1)&VALUE(SUBSTITUTE(MID(A1,FIND(" ",A1,FIND(" ",A1)+1),10),RIGHT(A1,2),""))

 

[Aladin Akyurek]

=LEFT(A1,FIND(" ",A1)-1)&TEXT(LEFT(REPLACE(A1,1,FIND(" ",A1),""),10),"yymmdd")&RIGHT(A1)&SUBSTITUTE(TRIM(REPLACE(A1,1,FIND("#",SUBSTITUTE(A1," ","#",2))-1,"")),RIGHT(A1),"")+0

where A1 houses a tring to convert.

 

[Nishant94]

Another way,

Unknown
	A	B
1	EXMPL1A 25/05/2018 2,150.00 D	EXMPL1A180525D2150
2	ABCD 15/06/2018 93.50 G	ABCD180615G93.5
3	PVHW3Q 12/07/2018 1450.00 I	PVHW3Q180712I1450
4	THQR 17/08/2019 65.25 K	THQR190817K65.25
Sheet8

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1)-1)&TEXT((MID(A1,FIND(" ",A1)+1,10)),"yymmdd")&RIGHT(A1,1)&MID(LEFT(A1,LEN(A1)-2),SEARCH("??/??/????",A1)+11,100)+0

 

[Peter_SSs]

JB

None of the suggestions so far work for me*, but that may well be due to different system date formats so one or all may work for you.
* For me, the above formulas all return "EXMPL1A05/25/2018D2150"

In case they don't work for you either, this worked for me, and should work on any date system if your sample data is representative.

Excel Workbook
	A	B
1	Data	Result
2	EXMPL1A 05/25/2018 2,150.00 D	EXMPL1A180525D2150
3	ABCD 06/15/2018 93.50 G	ABCD180615G93.5
4	PVHW3Q 07/12/2018 1450.00 I	PVHW3Q180712I1450
5	THQR 08/17/2019 65.25 K	THQR190817K65.25
Extract Info from text

 

[Aladin Akyurek]

It works for me...

Book1
	A	B	C
1	ABCD 06/15/2018 93.50 G	ABCD180615G93.5	ABCD180615G93.5
2	PVHW3Q 07/12/2018 1450.00 I	PVHW3Q180712I1450	PVHW3Q180712I1450
3	THQR 08/17/2019 65.25 K	THQR 190817K65.25	THQR190817K65.25
4	EXMPL1A 05/25/2018 2,150.00 D	EXMPL1A180525D2150	EXMPL1A180525D2150
5
6	input	desired output	formula result
7
Sheet1

Cell Formulas
Range	Formula
C1	=LEFT(A1,FIND(" ",A1)-1)&TEXT(LEFT(REPLACE(A1,1,FIND(" ",A1),""),10),"yymmdd")&RIGHT(A1)&SUBSTITUTE(TRIM(REPLACE(A1,1,FIND("#",SUBSTITUTE(A1," ","#",2))-1,"")),RIGHT(A1),"")+0

 

[Peter_SSs]

It works for me...

I don't doubt that. .. or that Erik's or Nishant's work for them. .. and they may well work for the OP. However, date issues can be sensitive to regional settings so I was offering an alternative that was not dependant on such settings.
Here's my sheet with the various options.

Excel Workbook
	A	B	C	D	E
1	Data	Peter	Erik	Aladin	Nishant
2	EXMPL1A 05/25/2018 2,150.00 D	EXMPL1A180525D2150	EXMPL1A05/25/2018D2150	EXMPL1A05/25/2018D2150	EXMPL1A05/25/2018D2150
3	ABCD 06/15/2018 93.50 G	ABCD180615G93.5	ABCD06/15/2018G93.5	ABCD06/15/2018G93.5	ABCD06/15/2018G93.5
4	PVHW3Q 07/12/2018 1450.00 I	PVHW3Q180712I1450	PVHW3Q181207I1450	PVHW3Q181207I1450	PVHW3Q181207I1450
5	THQR 08/17/2019 65.25 K	THQR190817K65.25	THQR08/17/2019K65.25	THQR08/17/2019K65.25	THQR08/17/2019K65.25
Extract Info from text

 

[Aladin Akyurek]

The real generality would be possible if Excel could parse a format specification in-between double quotes. Otherwise, handling date strings like

2015-05-20
2015/05/20
2015.05.20

and who knows what else would be thwarting to process.

 

[jbesr1230]

Many thanks to all of you - Erik, Aladin, Peter, Nishant94 !!. On my system, all your formulas worked. I'm not accomplished enough in working with text formulas to understand the discussion between Peter and Aladin; but that's why they're "MrExcel MVP" status and I'm glad they're willing to be so helpful.


Best to you all.

JB