=VALUE(CONCATENATE(A2,"/",B2,"/",C2," ",D2,":",E2,":", F2))
The Value function convert the text into numbers.
The Concatenate function connects all the text and convert them into one string.
Thursday, December 8, 2011
Concatenate text and convert to numbers in Excel
I have a long Excel file which keep the date, month, year, hour, minute, and second in separate columns (figure 1). I want to use only one column to present the time, so it would be easier to plot the time as x-axis. Here is the function I used in cell G2:
=VALUE(CONCATENATE(A2,"/",B2,"/",C2," ",D2,":",E2,":", F2))
The Value function convert the text into numbers.
The Concatenate function connects all the text and convert them into one string.
=VALUE(CONCATENATE(A2,"/",B2,"/",C2," ",D2,":",E2,":", F2))
The Value function convert the text into numbers.
The Concatenate function connects all the text and convert them into one string.
Subscribe to:
Post Comments (Atom)
-
It took me a while to figure out how to insert a space in Mathtype equations. This is especially useful when you write an equation with mult...
-
Recently I read post from Dr. Doug Hull's blog: http://blogs.mathworks.com/videos/2009/10/23/basics-volume-visualization-19-defining-s... -
To get the slope of a pair of x and y, usually I first plot the curve and then add the trend line. Actually there are two functions i...
% Read xls file
ReplyDeletevalues=xlsread('filename.xls','A2:G5')
% displaying time only
values(:,7)
Thanks for your comment!
DeleteIf you are concatenating stuff that is a mixture of text and numbers, and want only the valid concatenated number to show up, and only the valid text (not #value!) error when it is not a number, try this:
ReplyDeleteIF(ISNUMBER((VALUE(CONCATENATE(stuff,to,concatenate)))),((VALUE(CONCATENATE(stuff,to,concatenate)))),((CONCATENATE(stuff,to,concatenate)))))
How to convert numbers to word using concatenation in excel? A1=100.32 then B1=One hundred and thirty two centavos
ReplyDeleteThat is probably too hard to do in Excel...
DeleteI am also curious that in what case you will need to do this kind of conversion...
:)
Concatenating text and converting it to numbers is a common task in data manipulation. How Laptop Use In programming, it involves combining strings with numerical number.
ReplyDelete