Formatting Excel Texts and Values

K

kjxavier

New Member
Joined
Aug 11, 2011
Messages
21
Hi!

I have a Form to be filled and i receive the data as .txt files, hence have to convert data from txt to excel format for the ease of copying to my target form easily.

Now i have managed to convert the file to excel, but certain formats are really confusing.
I would be grateful if someone could help me out.

I have attached the excel file with all descriptions.......

Regards :confused:
 
Last edited:
Sort by date Sort by votes
Hi!

I have a Form to be filled and i receive the data as .txt files, hence have to convert data from txt to excel format for the ease of copying to my target form easily.

Now i have managed to convert the file to excel, but certain formats are really confusing.
I would be grateful if someone could help me out.

I have attached the excel file with all descriptions.......

Regards :confused:


<colgroup><col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:5339;width:110pt" width="146"> <col style="mso-width-source:userset;mso-width-alt:6326;width:130pt" width="173"> <col style="mso-width-source:userset;mso-width-alt:4681;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:1828;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:28123;width:577pt" width="769"> </colgroup><tbody>
[TD="class: xl70, width: 73"]Sl. No.[/TD]
[TD="class: xl70, width: 146"]Field Name[/TD]
[TD="class: xl73, width: 173"] [/TD]
[TD="class: xl70, width: 178, colspan: 2"]Result[/TD]
[TD="class: xl73, width: 769"]Description[/TD]

[TD="class: xl71"] [/TD]
[TD="class: xl71"] [/TD]
[TD="class: xl73"]Name[/TD]
[TD="class: xl73"]1[/TD]
[TD="class: xl72, align: right"]2[/TD]
[TD="class: xl73"] [/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]Applicant ID[/TD]
[TD="class: xl68"]George Kriss[/TD]
[TD="class: xl68"]G K[/TD]

[TD="class: xl68"]Only the first letter of First Name and Last Name from C4 must be displayed in Column D4 in Upper Case, with 5 spaces in b/w[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]george kriss[/TD]
[TD="class: xl68"]G K[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]GEORGE KRISS[/TD]
[TD="class: xl68"]G K[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]GEORGE KRISS JR[/TD]
[TD="class: xl68"]G K[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]GEORGE KRISS, JR[/TD]
[TD="class: xl68"]G K[/TD]
[TD="class: xl67"]JR[/TD]
[TD="class: xl68"]If Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Applicant Name[/TD]
[TD="class: xl68"]CurtisBlair[/TD]
[TD="class: xl68"]Curtis Blair[/TD]

[TD="class: xl68"] However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]Curtis Blair[/TD]
[TD="class: xl68"]Curtis Blair[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]CURTIS BLAIR[/TD]
[TD="class: xl68"]Curtis Blair[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]Curtis Blair[/TD]
[TD="class: xl68"]Curtis Blair[/TD]

[TD="class: xl68"]"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Co-Applicant Name[/TD]
[TD="class: xl68"]Lisa Blair[/TD]
[TD="class: xl68"]Lisa Blair[/TD]

[TD="class: xl68"]If anything displayed on C16, result Full Name at D16 (format at D16)[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl68"]N/A[/TD]

[TD="class: xl68"]If nothing is displayed on B16, then C16 should display N/A in Upper Case[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Applicant - First Name[/TD]
[TD="class: xl68"]George Kriss[/TD]
[TD="class: xl66"]George[/TD]

[TD="class: xl68"]The result in D19 displays only the First Name with First Letter Caps from C19[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Middle Name[/TD]
[TD="class: xl66"]Curtis Blair[/TD]
[TD="class: xl66"]B[/TD]

[TD="class: xl68"]D21 should display Second Names First letter from C21 in Caps "B"[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]Address [/TD]
[TD="class: xl66"]125 North Suite, 5th Street[/TD]
[TD="class: xl66"]125 NORTH SUITE,…[/TD]

[TD="class: xl66"]D23 should display everything in CAPS from C23[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl65"]City[/TD]
[TD="class: xl66"]New York[/TD]
[TD="class: xl66"]new york[/TD]

[TD="class: xl66"]D25 displays everything in Lower case from C25[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"]Phone[/TD]
[TD="class: xl66"]9037015967[/TD]
[TD="class: xl66"]90 370 159 69[/TD]

[TD="class: xl66"]D27 returns content of C27 in specified format[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl65"]Purchase price[/TD]
[TD="class: xl74"]$156,000 [/TD]
[TD="class: xl79"]$156,000.00 [/TD]

[TD="class: xl66"]D29 returns content of C29 in specified format[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl65"]Cashout Amount[/TD]
[TD="class: xl75"]10,000.00[/TD]
[TD="class: xl66"]10000[/TD]

[TD="class: xl68"]Result provides no decimals and no coma (,) seperators in column D31 form column C31[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl76"]10,000[/TD]
[TD="class: xl66"]10000[/TD]

[TD="class: xl68"]Result provides no decimals and no coma (,) seperators in column D32 form column C32[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]11[/TD]
[TD="class: xl69"]Years with Employer[/TD]
[TD="class: xl66"]2.3[/TD]
[TD="class: xl66"]2.3[/TD]

[TD="class: xl68"]D34 displays result from C34 with 1 decimal point[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl69"]Income[/TD]
[TD="class: xl76"]25,000[/TD]
[TD="class: xl66"]25000[/TD]

[TD="class: xl68"]No decimals ; no coma (,) seperators[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]25000[/TD]
[TD="class: xl66"]25000[/TD]

[TD="class: xl68"]No decimals ; no coma (,) seperators[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]13[/TD]
[TD="class: xl69"]Score[/TD]
[TD="class: xl66"]670[/TD]
[TD="class: xl66"]670[/TD]

[TD="class: xl68"]Only three digit Numbers[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]14[/TD]
[TD="class: xl69"]Call back time[/TD]
[TD="class: xl77"]18:00[/TD]
[TD="class: xl77"]18:00[/TD]

[TD="class: xl68"]Military time[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]15[/TD]
[TD="class: xl69"]Call back date[/TD]
[TD="class: xl78"]7/20/2004[/TD]
[TD="class: xl78"]7/22/2004[/TD]

[TD="class: xl68"]D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]16[/TD]
[TD="class: xl69"]Call back phone[/TD]
[TD="class: xl68"]90 370 159 67[/TD]
[TD="class: xl66"]9037015967[/TD]

[TD="class: xl68"]Whichever format the number appears, D45 returns numbers without spaces[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]9037015967[/TD]
[TD="class: xl66"]9037015967[/TD]

[TD="class: xl68"]Whichever format the number appears, D45 returns numbers without spaces[/TD]

[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][/TD]

[TD="class: xl65"]17[/TD]
[TD="class: xl65"]First Name Ini[/TD]
[TD="class: xl68"]GEORGE[/TD]
[TD="class: xl68"]G[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68"]Display the First Letter of C48 in D48 in CAPS[/TD]

</tbody>
[TABLE="width: 1339"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sl. No.[/TD]
[TD]Field Name[/TD]
[TD] [/TD]
[TD="colspan: 2"]Result[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Applicant ID[/TD]
[TD]George Kriss[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]Only the first letter of First Name and Last Name from C4 must be displayed in Column D4 in Upper Case, with 5 spaces in b/w[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]george kriss[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS JR[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS, JR[/TD]
[TD]G K[/TD]
[TD]JR[/TD]
[TD]If Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Applicant Name[/TD]
[TD]CurtisBlair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD] However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Curtis Blair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CURTIS BLAIR[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Curtis Blair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Co-Applicant Name[/TD]
[TD]Lisa Blair[/TD]
[TD]Lisa Blair[/TD]
[TD][/TD]
[TD]If anything displayed on C16, result Full Name at D16 (format at D16)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]If nothing is displayed on B16, then C16 should display N/A in Upper Case[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Applicant - First Name[/TD]
[TD]George Kriss[/TD]
[TD]George[/TD]
[TD][/TD]
[TD]The result in D19 displays only the First Name with First Letter Caps from C19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Middle Name[/TD]
[TD]Curtis Blair[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D21 should display Second Names First letter from C21 in Caps "B"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Address [/TD]
[TD]125 North Suite, 5th Street[/TD]
[TD]125 NORTH SUITE,…[/TD]
[TD][/TD]
[TD]D23 should display everything in CAPS from C23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]City[/TD]
[TD]New York[/TD]
[TD]new york[/TD]
[TD][/TD]
[TD]D25 displays everything in Lower case from C25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Phone[/TD]
[TD]9037015967[/TD]
[TD]90 370 159 69[/TD]
[TD][/TD]
[TD]D27 returns content of C27 in specified format[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Purchase price[/TD]
[TD]$156,000[/TD]
[TD]$156,000.00[/TD]
[TD][/TD]
[TD]D29 returns content of C29 in specified format[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cashout Amount[/TD]
[TD]10,000.00[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]Result provides no decimals and no coma (,) seperators in column D31 form column C31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10,000[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]Result provides no decimals and no coma (,) seperators in column D32 form column C32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Years with Employer[/TD]
[TD]2.3[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD]D34 displays result from C34 with 1 decimal point[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Income[/TD]
[TD]25,000[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]No decimals ; no coma (,) seperators[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]No decimals ; no coma (,) seperators[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Score[/TD]
[TD]670[/TD]
[TD]670[/TD]
[TD][/TD]
[TD]Only three digit Numbers[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Call back time[/TD]
[TD]18:00[/TD]
[TD]18:00[/TD]
[TD][/TD]
[TD]Military time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Call back date[/TD]
[TD]7/20/2004[/TD]
[TD]7/22/2004[/TD]
[TD][/TD]
[TD]D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Call back phone[/TD]
[TD]90 370 159 67[/TD]
[TD]9037015967[/TD]
[TD][/TD]
[TD]Whichever format the number appears, D45 returns numbers without spaces[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9037015967[/TD]
[TD]9037015967[/TD]
[TD][/TD]
[TD]Whichever format the number appears, D45 returns numbers without spaces[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]First Name Ini[/TD]
[TD]GEORGE[/TD]
[TD]G[/TD]
[TD][/TD]
[TD]Display the First Letter of C48 in D48 in CAPS[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry to note that i cant attach files here.....

am pasting the Excel file here....

[TABLE="width: 1339"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sl. No.[/TD]
[TD]Field Name[/TD]
[TD] [/TD]
[TD="colspan: 2"]Result[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]1[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Applicant ID[/TD]
[TD]George Kriss[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]Only the first letter of First Name and Last Name from C4 must be displayed in Column D4 in Upper Case, with 5 spaces in b/w[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]george kriss[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS JR[/TD]
[TD]G K[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GEORGE KRISS, JR[/TD]
[TD]G K[/TD]
[TD]JR[/TD]
[TD]If Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Applicant Name[/TD]
[TD]CurtisBlair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD] However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Curtis Blair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CURTIS BLAIR[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Curtis Blair[/TD]
[TD]Curtis Blair[/TD]
[TD][/TD]
[TD]"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Co-Applicant Name[/TD]
[TD]Lisa Blair[/TD]
[TD]Lisa Blair[/TD]
[TD][/TD]
[TD]If anything displayed on C16, result Full Name at D16 (format at D16)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]If nothing is displayed on B16, then C16 should display N/A in Upper Case[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Applicant - First Name[/TD]
[TD]George Kriss[/TD]
[TD]George[/TD]
[TD][/TD]
[TD]The result in D19 displays only the First Name with First Letter Caps from C19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Middle Name[/TD]
[TD]Curtis Blair[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D21 should display Second Names First letter from C21 in Caps "B"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Address [/TD]
[TD]125 North Suite, 5th Street[/TD]
[TD]125 NORTH SUITE,…[/TD]
[TD][/TD]
[TD]D23 should display everything in CAPS from C23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]City[/TD]
[TD]New York[/TD]
[TD]new york[/TD]
[TD][/TD]
[TD]D25 displays everything in Lower case from C25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Phone[/TD]
[TD]9037015967[/TD]
[TD]90 370 159 69[/TD]
[TD][/TD]
[TD]D27 returns content of C27 in specified format[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Purchase price[/TD]
[TD]$156,000[/TD]
[TD]$156,000.00[/TD]
[TD][/TD]
[TD]D29 returns content of C29 in specified format[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Cashout Amount[/TD]
[TD]10,000.00[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]Result provides no decimals and no coma (,) seperators in column D31 form column C31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10,000[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]Result provides no decimals and no coma (,) seperators in column D32 form column C32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Years with Employer[/TD]
[TD]2.3[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD]D34 displays result from C34 with 1 decimal point[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Income[/TD]
[TD]25,000[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]No decimals ; no coma (,) seperators[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]No decimals ; no coma (,) seperators[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Score[/TD]
[TD]670[/TD]
[TD]670[/TD]
[TD][/TD]
[TD]Only three digit Numbers[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Call back time[/TD]
[TD]18:00[/TD]
[TD]18:00[/TD]
[TD][/TD]
[TD]Military time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Call back date[/TD]
[TD]7/20/2004[/TD]
[TD]7/22/2004[/TD]
[TD][/TD]
[TD]D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Call back phone[/TD]
[TD]90 370 159 67[/TD]
[TD]9037015967[/TD]
[TD][/TD]
[TD]Whichever format the number appears, D45 returns numbers without spaces[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9037015967[/TD]
[TD]9037015967[/TD]
[TD][/TD]
[TD]Whichever format the number appears, D45 returns numbers without spaces[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]First Name Ini[/TD]
[TD]GEORGE[/TD]
[TD]G[/TD]
[TD][/TD]
[TD]Display the First Letter of C48 in D48 in CAPS[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How do I convert a messy .txt file to excel?
Replies
7
Views
247
Joe4
Joe4
G
  • Question Question
Best or only version of Excel for this purpose
Replies
2
Views
213
ginotx
G
J
  • Solved
Problems with Conditional Formatting in Pivot Tables in Excel 2024
Replies
3
Views
390
jfrsanders
J
U
  • Question Question
Convert Multiple (Matrix and 3 columns) to 5 columns List.
Replies
1
Views
88
Peter_SSs
Peter_SSs
D
  • Solved
cdate conversion from forms text box submission date errors if blank
Replies
3
Views
138
dmt32
D

Forum statistics

Threads
1,226,856
Messages
6,193,377
Members
453,792
Latest member
Vic001

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top