look at this attachment....the data are get together...i want to have each data in specific column...because i want to draw diagrams.....
please help me
i want for example time in one column and extension in another column.....
سلام.
فایل اکسل را بازش کنید، تمام سل هایی که دیتاها توش هست را انتخاب کنید و کپی کنید توی یک فایل با پسوند تکست(از طریق برنامه نوت پد ویندوز).
حالا برنامه اکسل را باز کنید و فایل تکسی که ذخیره کردید را با اکسل بازش کنید. یه پنجره تنظیمات نشون میده که برای فایل شما باید بگید کاما جدا کننده دیتا هستش. دیتاها بصورت جداگانه توی ستون خودش مرتب میشه.
Here are two ways you could do this. One is based on a function perspective and the other is based on a feature of Excel.
1. Use SUBSTITUTE, IFERROR(FIND), and VALUE(MID):
SUBSTITUTE replaces particular characters or substrings with what you desire. In this case, we want to substitute every quotation mark (CHAR(34)) with a 'blank' "". In other words, we get down to just commas. =SUBSTITUTE(A1,CHAR(34),"").
FIND then can be used to determine the position of the break points (those commas). FIND starts with what you want to search for (","), then has the text, and then what position to start at. To find the second and beyond commas, we would use the previous answer + 1 to get the new position. For the last one, you will get a #VALUE! error. To account for that, the function would also use an IFERROR. =IFERROR(FIND(",",$B1,C1+1),LEN($B1)+1). The second part of that returns the position for the last character and assumes there would be a comma after it.
From here, we use the MID function, which extracts text. I would suggest also surrounding this with VALUE as that will produce numbers rather than text (if your purpose is text, then you can omit that). VALUE(MID(B1,position of the comma+1,difference in positions -1))
And you now have split values using only functions.
2. Text to Columns
Excel has a feature called Text to Columns. This is just like importing a text file, but uses the infrastructure of a spreadsheet cell to differentiate the lines of text. Using the original text, select delimited, click next, select the comma option and also select the other option and type a ". This will split the columns as you wish. It is much simpler than doing it with functions.
- Badges
- Science topic
- Similar topics
- Analytical Chemistry
- Column