I want to extract some text if a following data in the cells:
salutation login ID email password first_name middle_name last_name gender date_of_birth account_status user_address1 user_address2 city postal_code country telephone id_card_number role
Mr. bawan 1 [email protected] bbbbbb Bashir Awan male 18-07-1988 active 55-B Faisal Town Lahore 54000 Pakistan 0423-5599229 34220-0353392-1 admin
Mr. njawad 2 [email protected] nnnnnn Nasir Jawad male 21-03-1979 active 193 SherShah Colony Gujranwala 54000 Pakistan 0319-2292299 35210-3523327-2 supervisor
Dr. hakhan 3 [email protected] hhhhhh faisal Aslam Khan male 12-05-1973 active House# 77 Mumtaz Street, Sham Nagar Gujrat 54000 Pakistan 0349-8280022 34220-0353392-1 admin
Ms. tnoor 4 [email protected] tttttt Tayyaba Noor female 15-01-1980 active 483 Ghazanvi Road Faisalabad 54000 Pakistan 0381-5589333 34220-0353392-1 marketing executive
This the data as show above its correctly formatted in excel it show a bit tidy.
This is what I am doing:
=CONCATENATE("{:salutation=>", A2 , ", :login =>",B2," , :ID=>", C2,", :email =>",D2,", :password=>",E2,", :first_name=>",F2,", :middle_name=>",G2,", :last_name=>",H2,", :gender =>",I2," , data_of_birth=>",J2,", :account_status=>",K2,", :user_address1=>",L2,", :user_address2=>",M2,", :city=>",N2,", :postal_code=>",O2,", :country=>",P2,", :telephone=>",Q2,", :id_card_number=>",R2,", :role=>",S2,"},")
and I got this in result:
{:salutation=>Mr., :login =>bawan , :ID=>1, :email =>[email protected], :password=>bawan1, :first_name=>Bashir, :middle_name=> , :last_name=>Awan, :gender =>male , data_of_birth=>18-07-1988, :account_status=>active, :user_address1=>55-B, :user_address2=>Faisal Town, :city=>Lahore, :postal_code=>54000, :country=>Pakistan, :telephone=>0423-5599229, :id_card_number=>34220-0353392-1, :role=>admin},
But I want this to be generated:
{:salutation=>"Mr.", :login =>"bawan" , :ID=>"1", :email =>"[email protected]", :password=>"bawan1", :first_name=>"Bashir", :middle_name=>"" , :last_name=>"Awan", :gender =>"male" , data_of_birth=>"18-07-1988", :account_status=>"active", :user_address1=>"55-B", :user_address2=>"Faisal Town", :city=>"Lahore", :postal_code=>"54000", :country=>"Pakistan", :telephone=>"0423-5599229", :id_card_number=>"34220-0353392-1", :role=>"admin"},
So what to change in my
=CONCATENATE(code)
to generate the above!
Quotes have a special meaning in excel formulas. Try using CHAR(34) instead. It's not 'pretty', but that's the workaround I found:
=CONCATENATE("{:salutation=>",CHAR(34),A2,CHAR(34),", :login =>",CHAR(34),B2,CHAR(34)," , :ID=>",CHAR(34),C2,CHAR(34),", :email =>",CHAR(34),D2,CHAR(34),", :password=>",CHAR(34),E2,CHAR(34),", :first_name=>",CHAR(34),F2,CHAR(34),", :middle_name=>",CHAR(34),G2,CHAR(34),", :last_name=>",CHAR(34),H2,CHAR(34),", :gender =>",CHAR(34),I2,CHAR(34)," , data_of_birth=>",CHAR(34),J2,CHAR(34),", :account_status=>",CHAR(34),K2,CHAR(34),", :user_address1=>",CHAR(34),L2,CHAR(34),", :user_address2=>",CHAR(34),M2,CHAR(34),", :city=>",CHAR(34),N2,CHAR(34),", :postal_code=>",CHAR(34),O2,CHAR(34),", :country=>",CHAR(34),P2,CHAR(34),", :telephone=>",CHAR(34),Q2,CHAR(34),", :id_card_number=>",CHAR(34),R2,CHAR(34),", :role=>",CHAR(34),S2,,CHAR(34),"},")
EDIT: You can escape quotes with "" (this excludes the quotes to indicate text):
=CONCATENATE("{:salutation=>""",A2,""", :login =>""",B2,""" , :ID=>""",C2,""", :email =>""",D2,""", :password=>""",E2,""", :first_name=>""",F2,""", :middle_name=>""",G2,""", :last_name=>""",H2,""", :gender =>""",I2,""" , data_of_birth=>""",J2,""", :account_status=>""",K2,""", :user_address1=>""",L2,""", :user_address2=>""",M2,""", :city=>""",N2,""", :postal_code=>""",O2,""", :country=>""",P2,""", :telephone=>""",Q2,""", :id_card_number=>""",R2,""", :role=>""",S2,"""},")
Thanks JustinJDavies for pointing this out :)