convert text to date and copy in another column

B

Balkhair

New Member
Joined
Oct 24, 2019
Messages
6
Dear Experts,

I need a formula to convert text to date and copy in column A. Below is the data for reference.

[TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"][TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"][TABLE="width: 136"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"]15/09/2019(Sunday)[/TD]
[/TR]
[TR]
[TD]SnoG0111[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG014[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG018[/TD]
[/TR]
[TR]
[TD]SnoG020[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG021[/TD]
[/TR]
[TR]
[TD]SnoG033[/TD]
[/TR]
[TR]
[TD]SnoG040[/TD]
[/TR]
[TR]
[TD]SnoG044[/TD]
[/TR]
[TR]
[TD]SnoG055[/TD]
[/TR]
[TR]
[TD]SnoG058[/TD]
[/TR]
[TR]
[TD]SnoG062[/TD]
[/TR]
[TR]
[TD]SnoG070[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]16/09/2019(Monday)[/TD]
[/TR]
[TR]
[TD]SnoG0111[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG014[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG018[/TD]
[/TR]
[TR]
[TD]SnoG020[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG021[/TD]
[/TR]
[TR]
[TD]SnoG033[/TD]
[/TR]
[TR]
[TD]SnoG040[/TD]
[/TR]
[TR]
[TD]SnoG044[/TD]
[/TR]
[TR]
[TD]PBU0055[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]17/09/2019(Tuesday)[/TD]
[/TR]
[TR]
[TD]SnoG0111[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG014[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG018[/TD]
[/TR]
[TR]
[TD]SnoG020[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG021[/TD]
[/TR]
[TR]
[TD]SnoG033[/TD]
[/TR]
[TR]
[TD]SnoG040[/TD]
[/TR]
[TR]
[TD]SnoG044[/TD]
[/TR]
[TR]
[TD]SnoG055[/TD]
[/TR]
[TR]
[TD]SnoG058[/TD]
[/TR]
[TR]
[TD]SnoG062[/TD]
[/TR]
[TR]
[TD]SnoG070[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG076[/TD]
[/TR]
[TR]
[TD]SnoG077[/TD]
[/TR]
[TR]
[TD]SnoG079[/TD]
[/TR]
[TR]
[TD]SnoG091[/TD]
[/TR]
[TR]
[TD]SnoG092[/TD]
[/TR]
[TR]
[TD]SnoG093[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG094[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG097[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]SnoG107[/TD]
[/TR]
[TR]
[TD]SnoG119[/TD]
[/TR]
[TR]
[TD]SnoG124[/TD]
[/TR]
[TR]
[TD]SnoG126[/TD]
[/TR]
[TR]
[TD]SnoG127[/TD]
[/TR]
[TR]
[TD]SnoG128[/TD]
[/TR]
[TR]
[TD]SnoG129[/TD]
[/TR]
[TR]
[TD]SnoG130[/TD]
[/TR]
[TR]
[TD]BC003[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]17/09/2019(Tuesday)[/TD]
[/TR]
[TR]
[TD]IHD019[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]PBU0045[/TD]
[/TR]
[TR]
[TD]PBU0055


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
The expected output result is something like this.


[TABLE="width: 207"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]15/09/2019(Sunday)[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG0111[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG014[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG018[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG020[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG021[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG033[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG040[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG044[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG055[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG058[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG062[/TD]
[/TR]
[TR]
[TD="align: center"]15/09/2019[/TD]
[TD="align: center"]SnoG070[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]16/09/2019(Monday)[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG0111[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG014[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG018[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG020[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG021[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG033[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG040[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]SnoG044[/TD]
[/TR]
[TR]
[TD="align: center"]16/09/2019[/TD]
[TD="align: center"]PBU0055[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]17/09/2019(Tuesday)[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG0111[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG014[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG018[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG020[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG021[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG033[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG040[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG044[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG055[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG058[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG062[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]SnoG070[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG076[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG077[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG079[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG091[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG092[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG093[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG094[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG097[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG107[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG119[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG124[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG126[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG127[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG128[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG129[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]SnoG130[/TD]
[/TR]
[TR]
[TD="align: center"]17/09/2019[/TD]
[TD="align: center"]BC003[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Using Power Query/Get and Transform here is the Mcode. If unfamiliar with PQ, click on the link in my signature.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each if Text.Contains([Column1.1],"/") then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Column1.1"})
in
    #"Reordered Columns"

Data Range
[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][/tr]
[tr][td]
1​
[/td][td]
Date​
[/td][td]
Column1.1​
[/td][/tr]


[tr][td]
2​
[/td][td]
15/09/2019​
[/td][td]
15/09/2019​
[/td][/tr]


[tr][td]
3​
[/td][td]
15/09/2019​
[/td][td]
SnoG0111​
[/td][/tr]


[tr][td]
4​
[/td][td]
15/09/2019​
[/td][td]
SnoG014​
[/td][/tr]


[tr][td]
5​
[/td][td]
15/09/2019​
[/td][td]
SnoG018​
[/td][/tr]


[tr][td]
6​
[/td][td]
15/09/2019​
[/td][td]
SnoG020​
[/td][/tr]


[tr][td]
7​
[/td][td]
15/09/2019​
[/td][td]
SnoG021​
[/td][/tr]


[tr][td]
8​
[/td][td]
15/09/2019​
[/td][td]
SnoG033​
[/td][/tr]


[tr][td]
9​
[/td][td]
15/09/2019​
[/td][td]
SnoG040​
[/td][/tr]


[tr][td]
10​
[/td][td]
15/09/2019​
[/td][td]
SnoG044​
[/td][/tr]


[tr][td]
11​
[/td][td]
15/09/2019​
[/td][td]
SnoG055​
[/td][/tr]


[tr][td]
12​
[/td][td]
15/09/2019​
[/td][td]
SnoG058​
[/td][/tr]


[tr][td]
13​
[/td][td]
15/09/2019​
[/td][td]
SnoG062​
[/td][/tr]


[tr][td]
14​
[/td][td]
15/09/2019​
[/td][td]
SnoG070​
[/td][/tr]


[tr][td]
15​
[/td][td]
16/09/2019​
[/td][td]
16/09/2019​
[/td][/tr]


[tr][td]
16​
[/td][td]
16/09/2019​
[/td][td]
SnoG0111​
[/td][/tr]


[tr][td]
17​
[/td][td]
16/09/2019​
[/td][td]
SnoG014​
[/td][/tr]


[tr][td]
18​
[/td][td]
16/09/2019​
[/td][td]
SnoG018​
[/td][/tr]


[tr][td]
19​
[/td][td]
16/09/2019​
[/td][td]
SnoG020​
[/td][/tr]


[tr][td]
20​
[/td][td]
16/09/2019​
[/td][td]
SnoG021​
[/td][/tr]


[tr][td]
21​
[/td][td]
16/09/2019​
[/td][td]
SnoG033​
[/td][/tr]


[tr][td]
22​
[/td][td]
16/09/2019​
[/td][td]
SnoG040​
[/td][/tr]


[tr][td]
23​
[/td][td]
16/09/2019​
[/td][td]
SnoG044​
[/td][/tr]


[tr][td]
24​
[/td][td]
16/09/2019​
[/td][td]
PBU0055​
[/td][/tr]


[tr][td]
25​
[/td][td]
17/09/2019​
[/td][td]
17/09/2019​
[/td][/tr]


[tr][td]
26​
[/td][td]
17/09/2019​
[/td][td]
SnoG0111​
[/td][/tr]


[tr][td]
27​
[/td][td]
17/09/2019​
[/td][td]
SnoG014​
[/td][/tr]


[tr][td]
28​
[/td][td]
17/09/2019​
[/td][td]
SnoG018​
[/td][/tr]


[tr][td]
29​
[/td][td]
17/09/2019​
[/td][td]
SnoG020​
[/td][/tr]


[tr][td]
30​
[/td][td]
17/09/2019​
[/td][td]
SnoG021​
[/td][/tr]


[tr][td]
31​
[/td][td]
17/09/2019​
[/td][td]
SnoG033​
[/td][/tr]


[tr][td]
32​
[/td][td]
17/09/2019​
[/td][td]
SnoG040​
[/td][/tr]


[tr][td]
33​
[/td][td]
17/09/2019​
[/td][td]
SnoG044​
[/td][/tr]


[tr][td]
34​
[/td][td]
17/09/2019​
[/td][td]
SnoG055​
[/td][/tr]


[tr][td]
35​
[/td][td]
17/09/2019​
[/td][td]
SnoG058​
[/td][/tr]


[tr][td]
36​
[/td][td]
17/09/2019​
[/td][td]
SnoG062​
[/td][/tr]


[tr][td]
37​
[/td][td]
17/09/2019​
[/td][td]
SnoG070​
[/td][/tr]


[tr][td]
38​
[/td][td]
17/09/2019​
[/td][td]
SnoG076​
[/td][/tr]


[tr][td]
39​
[/td][td]
17/09/2019​
[/td][td]
SnoG077​
[/td][/tr]


[tr][td]
40​
[/td][td]
17/09/2019​
[/td][td]
SnoG079​
[/td][/tr]


[tr][td]
41​
[/td][td]
17/09/2019​
[/td][td]
SnoG091​
[/td][/tr]


[tr][td]
42​
[/td][td]
17/09/2019​
[/td][td]
SnoG092​
[/td][/tr]


[tr][td]
43​
[/td][td]
17/09/2019​
[/td][td]
SnoG093​
[/td][/tr]


[tr][td]
44​
[/td][td]
17/09/2019​
[/td][td]
SnoG094​
[/td][/tr]


[tr][td]
45​
[/td][td]
17/09/2019​
[/td][td]
SnoG097​
[/td][/tr]


[tr][td]
46​
[/td][td]
17/09/2019​
[/td][td]
SnoG107​
[/td][/tr]


[tr][td]
47​
[/td][td]
17/09/2019​
[/td][td]
SnoG119​
[/td][/tr]


[tr][td]
48​
[/td][td]
17/09/2019​
[/td][td]
SnoG124​
[/td][/tr]


[tr][td]
49​
[/td][td]
17/09/2019​
[/td][td]
SnoG126​
[/td][/tr]


[tr][td]
50​
[/td][td]
17/09/2019​
[/td][td]
SnoG127​
[/td][/tr]


[tr][td]
51​
[/td][td]
17/09/2019​
[/td][td]
SnoG128​
[/td][/tr]


[tr][td]
52​
[/td][td]
17/09/2019​
[/td][td]
SnoG129​
[/td][/tr]


[tr][td]
53​
[/td][td]
17/09/2019​
[/td][td]
SnoG130​
[/td][/tr]


[tr][td]
54​
[/td][td]
17/09/2019​
[/td][td]
BC003​
[/td][/tr]


[tr][td]
55​
[/td][td]
17/09/2019​
[/td][td]
17/09/2019​
[/td][/tr]


[tr][td]
56​
[/td][td]
17/09/2019​
[/td][td]
IHD019​
[/td][/tr]


[tr][td]
57​
[/td][td]
17/09/2019​
[/td][td]
PBU0045​
[/td][/tr]


[tr][td]
58​
[/td][td]
17/09/2019​
[/td][td]
PBU0055​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Is this, copied down, sufficient?

<b>Balkhair</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:152px;" /><col style="width:161px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">15/09/2019(Sunday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG058</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG062</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">15/09/2019</td><td style="font-size:10pt; ">SnoG070</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">16/09/2019(Monday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; text-align:right; ">16/09/2019</td><td style="font-size:10pt; ">PBU0055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">17/09/2019(Tuesday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG0111</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG014</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG018</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG020</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG021</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG033</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG040</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG044</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG055</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG058</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG062</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG070</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG076</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG077</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG079</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG091</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG092</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >44</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG093</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >45</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG094</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >46</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG097</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >47</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG107</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >48</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG119</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >49</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG124</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >50</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG126</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >51</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG127</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >52</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG128</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >53</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG129</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >54</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">SnoG130</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >55</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">BC003</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >56</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >57</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">17/09/2019(Tuesday)</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >58</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">IHD019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >59</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">PBU0045</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >60</td><td style="font-size:10pt; text-align:right; ">17/09/2019</td><td style="font-size:10pt; ">PBU0055</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A1</td><td >=IF(B1="","",LEFT<span style=' color:008000; '>(LOOKUP<span style=' color:#0000ff; '>(4,LEFT<span style=' color:#ff0000; '>(B$1:B1,1)</span>+0,B$1:B1)</span>,10)</span>+0)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
Hello Sir,

I am new to QP and when i try to run the code after checking the video it gives me an error " Expression.Error: We couldn't find an Excel table named 'Table1'. "


alansidman said:
Using Power Query/Get and Transform here is the Mcode. If unfamiliar with PQ, click on the link in my signature.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each if Text.Contains([Column1.1],"/") then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Column1.1"})
in
    #"Reordered Columns"

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
Date​
[/TD]
[TD]
Column1.1​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
15/09/2019​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG0111​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG014​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG018​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG020​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG021​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG033​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG040​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG044​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG055​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG058​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG062​
[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]
15/09/2019​
[/TD]
[TD]
SnoG070​
[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
16/09/2019​
[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG0111​
[/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG014​
[/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG018​
[/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG020​
[/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG021​
[/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG033​
[/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG040​
[/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
SnoG044​
[/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]
16/09/2019​
[/TD]
[TD]
PBU0055​
[/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
17/09/2019​
[/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG0111​
[/TD]
[/TR]
[TR]
[TD]
27​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG014​
[/TD]
[/TR]
[TR]
[TD]
28​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG018​
[/TD]
[/TR]
[TR]
[TD]
29​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG020​
[/TD]
[/TR]
[TR]
[TD]
30​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG021​
[/TD]
[/TR]
[TR]
[TD]
31​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG033​
[/TD]
[/TR]
[TR]
[TD]
32​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG040​
[/TD]
[/TR]
[TR]
[TD]
33​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG044​
[/TD]
[/TR]
[TR]
[TD]
34​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG055​
[/TD]
[/TR]
[TR]
[TD]
35​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG058​
[/TD]
[/TR]
[TR]
[TD]
36​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG062​
[/TD]
[/TR]
[TR]
[TD]
37​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG070​
[/TD]
[/TR]
[TR]
[TD]
38​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG076​
[/TD]
[/TR]
[TR]
[TD]
39​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG077​
[/TD]
[/TR]
[TR]
[TD]
40​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG079​
[/TD]
[/TR]
[TR]
[TD]
41​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG091​
[/TD]
[/TR]
[TR]
[TD]
42​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG092​
[/TD]
[/TR]
[TR]
[TD]
43​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG093​
[/TD]
[/TR]
[TR]
[TD]
44​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG094​
[/TD]
[/TR]
[TR]
[TD]
45​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG097​
[/TD]
[/TR]
[TR]
[TD]
46​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG107​
[/TD]
[/TR]
[TR]
[TD]
47​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG119​
[/TD]
[/TR]
[TR]
[TD]
48​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG124​
[/TD]
[/TR]
[TR]
[TD]
49​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG126​
[/TD]
[/TR]
[TR]
[TD]
50​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG127​
[/TD]
[/TR]
[TR]
[TD]
51​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG128​
[/TD]
[/TR]
[TR]
[TD]
52​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG129​
[/TD]
[/TR]
[TR]
[TD]
53​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
SnoG130​
[/TD]
[/TR]
[TR]
[TD]
54​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
BC003​
[/TD]
[/TR]
[TR]
[TD]
55​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
17/09/2019​
[/TD]
[/TR]
[TR]
[TD]
56​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
IHD019​
[/TD]
[/TR]
[TR]
[TD]
57​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
PBU0045​
[/TD]
[/TR]
[TR]
[TD]
58​
[/TD]
[TD]
17/09/2019​
[/TD]
[TD]
PBU0055​
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Dear Sir,

The above formula is excellent. However, when i copy paste same it gives me an error "#VALUE#"


[TABLE="width: 236"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: center"]#VALUE![/TD]
[TD]15/09/2019(Sunday)[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG0111[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG014[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG018[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG020[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG021[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG033[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG040[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG044[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG055[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG058[/TD]
[/TR]
[TR]
[TD="align: center"]#VALUE![/TD]
[TD]AQHG062[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Your table may have a different name, which means you .will need to change the Mcode to reflect the proper table name. I used Table1
 
Upvote 0
Balkhair said:
The above formula is excellent. However, when i copy paste same it gives me an error "#VALUE#"
Click to expand...
Hard to be sure what the problem is without seeing the workbook. It could be that the data or layout is actually different to what I have as you can see it working in my sheet.

If still unable to resolve, perhaps you could upload a dummy sample file with the problem to a file-share site and provide a shared link to that file?
 
Upvote 0
Another option.
Make sure your data starts in cell B2, put the formula in A2 and copy down.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /><col style="width:192px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">15/09/2019</td><td >15/09/2019(Sunday)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">15/09/2019</td><td >SnoG0111</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">15/09/2019</td><td >SnoG014</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">15/09/2019</td><td >SnoG018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">15/09/2019</td><td >SnoG020</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">15/09/2019</td><td >SnoG021</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">15/09/2019</td><td >SnoG033</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">15/09/2019</td><td >SnoG040</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">15/09/2019</td><td >SnoG044</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">15/09/2019</td><td >SnoG055</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">15/09/2019</td><td >SnoG058</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">15/09/2019</td><td >SnoG062</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">15/09/2019</td><td >SnoG070</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">16/09/2019</td><td >16/09/2019(Monday)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">16/09/2019</td><td >SnoG0111</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">16/09/2019</td><td >SnoG014</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">16/09/2019</td><td >SnoG018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">16/09/2019</td><td >SnoG020</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=IF(B2="","",IF(MID(B2,3,1)="/",LEFT(B2,10)+0,A1))</td></tr></table></td></tr></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Countif formula for dates
Replies
2
Views
478
Oakey
O
B
  • Question Question
Formula or VBA for Biometrics attendance
Replies
1
Views
704
Balkhair
B
B
  • Question Question
Copying/Inserting Rows & Data from one sheet to another based on a date lookup
Replies
0
Views
478
Billman101
B
A
  • Question Question
VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook
Replies
6
Views
742
annuity
A

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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