Need help with a formula that removes text.
Hey all!
I just joined today and I'm in need with help for a formula. So what I am doing is that I am exporting my data and when I paste it.
The column has all the following in it:
01/25/2019 11:59 PM PST
I was seeing if there is anyway someone can help with a formula that gets rid of the PST. I haven't gotten anywhere with it and can't find anything.
Thank you!!
It depends on exactly what way the column is formatted. But, as a first step,
=LEFT(A1,SEARCH("PST",A1)-1)+0
and format as desired.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
=substitute(a1,"pst",)
The column when I paste it is General.
I used that formula and I just get a string of text instead.
Also the column does have spaces in it if that helps.
Last edited by Casaray; 01-28-2019 at 01:31 AM.
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).
1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!
2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
3. Make sure that your desired solution is also shown (mock up the results manually).
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
That will still return text, use -- (or +0) to convert...Originally Posted by Ghozi Alkatiri
=--SUBSTITUTE(G23," PST",)
Also note the addition of a leading space, and the use of CAPS
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Oftentimes when converting text dates/times to real dates/times, excel will try and "help" by reformatting the cell as text. Try reformatting it to date
Thank you! Reformatting it worked!
Thank you all for the help. One last question to ask,
It works but when I paste the data that I need, it removes the formula. Is there anyway to have that formula on another sheet to pull that information? The formula that I have can't pull data because of the PST, so I would only need the date/time.
If you are pasting the data to Sheet1 and pulling the date/time to Sheet2 you could try: =--SUBSTITUTE('Sheet1'!G23," PST",)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Or this
Formula:
Please Login or Register to view this content.
Format as Custom, mm/dd/yyyy hh:mm AM/PM
v A B 1 01/25/2019 11:59 PM PST 01/25/2019 11:59 PM
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks