Text Extraction

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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")
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's a cool trick I did not know.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

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