Formatting Excel Texts and Values

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:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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:

Sl. No.Field Name ResultDescription
Name12
1Applicant IDGeorge KrissG KOnly 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
george krissG K"
GEORGE KRISSG K"
GEORGE KRISS JRG K"
GEORGE KRISS, JRG KJRIf Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively
2Applicant NameCurtisBlairCurtis Blair However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w
Curtis BlairCurtis Blair"
CURTIS BLAIRCurtis Blair"
Curtis BlairCurtis Blair"
3Co-Applicant NameLisa BlairLisa BlairIf anything displayed on C16, result Full Name at D16 (format at D16)
N/AIf nothing is displayed on B16, then C16 should display N/A in Upper Case
4Applicant - First NameGeorge KrissGeorgeThe result in D19 displays only the First Name with First Letter Caps from C19
5Middle NameCurtis BlairBD21 should display Second Names First letter from C21 in Caps "B"
6Address 125 North Suite, 5th Street125 NORTH SUITE,…D23 should display everything in CAPS from C23
7CityNew Yorknew yorkD25 displays everything in Lower case from C25
8Phone903701596790 370 159 69D27 returns content of C27 in specified format
9Purchase price$156,000 $156,000.00 D29 returns content of C29 in specified format
10Cashout Amount10,000.0010000Result provides no decimals and no coma (,) seperators in column D31 form column C31
10,00010000Result provides no decimals and no coma (,) seperators in column D32 form column C32
11Years with Employer2.32.3D34 displays result from C34 with 1 decimal point
12Income25,00025000No decimals ; no coma (,) seperators
2500025000No decimals ; no coma (,) seperators
13Score670670Only three digit Numbers
14Call back time18:0018:00Military time
15Call back date7/20/20047/22/2004D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)
16Call back phone90 370 159 679037015967Whichever format the number appears, D45 returns numbers without spaces
90370159679037015967Whichever format the number appears, D45 returns numbers without spaces
17First Name IniGEORGEGDisplay the First Letter of C48 in D48 in CAPS

<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>
</tbody>
Sl. No.Field Name ResultDescription
Name12
1Applicant IDGeorge KrissG KOnly 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
george krissG K"
GEORGE KRISSG K"
GEORGE KRISS JRG K"
GEORGE KRISS, JRG KJRIf Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively
2Applicant NameCurtisBlairCurtis Blair However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w
Curtis BlairCurtis Blair"
CURTIS BLAIRCurtis Blair"
Curtis BlairCurtis Blair"
3Co-Applicant NameLisa BlairLisa BlairIf anything displayed on C16, result Full Name at D16 (format at D16)
N/AIf nothing is displayed on B16, then C16 should display N/A in Upper Case
4Applicant - First NameGeorge KrissGeorgeThe result in D19 displays only the First Name with First Letter Caps from C19
5Middle NameCurtis BlairBD21 should display Second Names First letter from C21 in Caps "B"
6Address 125 North Suite, 5th Street125 NORTH SUITE,…D23 should display everything in CAPS from C23
7CityNew Yorknew yorkD25 displays everything in Lower case from C25
8Phone903701596790 370 159 69D27 returns content of C27 in specified format
9Purchase price$156,000$156,000.00D29 returns content of C29 in specified format
10Cashout Amount10,000.0010000Result provides no decimals and no coma (,) seperators in column D31 form column C31
10,00010000Result provides no decimals and no coma (,) seperators in column D32 form column C32
11Years with Employer2.32.3D34 displays result from C34 with 1 decimal point
12Income25,00025000No decimals ; no coma (,) seperators
2500025000No decimals ; no coma (,) seperators
13Score670670Only three digit Numbers
14Call back time18:0018:00Military time
15Call back date7/20/20047/22/2004D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)
16Call back phone90 370 159 679037015967Whichever format the number appears, D45 returns numbers without spaces
90370159679037015967Whichever format the number appears, D45 returns numbers without spaces
17First Name IniGEORGEGDisplay the First Letter of C48 in D48 in CAPS

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

kjxavier

New Member
Joined
Aug 11, 2011
Messages
21
Sorry to note that i cant attach files here.....

am pasting the Excel file here....

Sl. No.Field Name ResultDescription
Name12
1Applicant IDGeorge KrissG KOnly 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
george krissG K"
GEORGE KRISSG K"
GEORGE KRISS JRG K"
GEORGE KRISS, JRG KJRIf Name is followed by a 'Coma' [,] along with 'JR' in any case. Above criteria as in F4 + JR in column E8 respectively
2Applicant NameCurtisBlairCurtis Blair However style the Name appears in Column C , corresponding D coulmn Should return Full Name as in D11 with 1 space in b/w
Curtis BlairCurtis Blair"
CURTIS BLAIRCurtis Blair"
Curtis BlairCurtis Blair"
3Co-Applicant NameLisa BlairLisa BlairIf anything displayed on C16, result Full Name at D16 (format at D16)
N/AIf nothing is displayed on B16, then C16 should display N/A in Upper Case
4Applicant - First NameGeorge KrissGeorgeThe result in D19 displays only the First Name with First Letter Caps from C19
5Middle NameCurtis BlairBD21 should display Second Names First letter from C21 in Caps "B"
6Address 125 North Suite, 5th Street125 NORTH SUITE,…D23 should display everything in CAPS from C23
7CityNew Yorknew yorkD25 displays everything in Lower case from C25
8Phone903701596790 370 159 69D27 returns content of C27 in specified format
9Purchase price$156,000$156,000.00D29 returns content of C29 in specified format
10Cashout Amount10,000.0010000Result provides no decimals and no coma (,) seperators in column D31 form column C31
10,00010000Result provides no decimals and no coma (,) seperators in column D32 form column C32
11Years with Employer2.32.3D34 displays result from C34 with 1 decimal point
12Income25,00025000No decimals ; no coma (,) seperators
2500025000No decimals ; no coma (,) seperators
13Score670670Only three digit Numbers
14Call back time18:0018:00Military time
15Call back date7/20/20047/22/2004D43 contains date, three days from the day the form C43 (i.e. from 20,21,22)
16Call back phone90 370 159 679037015967Whichever format the number appears, D45 returns numbers without spaces
90370159679037015967Whichever format the number appears, D45 returns numbers without spaces
17First Name IniGEORGEGDisplay the First Letter of C48 in D48 in CAPS

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Forum statistics

Threads
1,137,155
Messages
5,679,923
Members
419,862
Latest member
Bluewings666

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
Top