Text Extraction

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. Windows
ExcelDateTimeExtraction.png
MessageReceived.png

During the course of extracting the date and time data from this Excel sheet into a word document it adds a tab character between 2020, the @ and the time, in this case, 11:40.
So I first tried using the concatenate but it inserts numbers instead of date and time formats. Changing the format of the formula result into date and time does nothing. Then I tried extracting the numbers left of and right of the @ and set those formula results to date and time formats respectively and still nothing.
What I am trying to accomplish is to when I copy the three cells, in this case, E4, F4, and G4 together and paste together in Microsoft Word 2013 I want the tab characters to be replaced with 2 dashes (--) which is what I ultimately have to do in word using the replace feature, as seen in this third image.
MessageReceived2.png

If one or more formulas could auto-input into one cell the data from E4, F4, and G4 and insert the double dash between the date, @, and time that would be awesome.
Thanks for any help provided.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi,

The data and time in columns E/G are only formatted as data and time, the content is always a number.
Concatenation like you want can be done by forcing a conversion to Text and apply a correct format:
=TEXTJOIN(" ",FALSE,TEXT(E4,"ddd dd/mmm/yyyy"),F4,TEXT(G4,"hh:mm"))
or = TEXT(E4,"ddd dd/mmm/yyyy")&" "& F4&" "&TEXT(G4,"hh:mm")

EDIT: to have weekday appear like "Sat", you might need to tweak LEFT(TEXT(E4,"dddd"),3) & " " & TEXT(E4,"dd/mmm/yyyy")
 
Upvote 0
That's a cool trick I did not know.
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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