Convert Excel Formula to Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some raw Excel data in the format:
2170696969
2021965
2023241
2241710101
2018501
2050183

The data needs to end up taking the area code from the previous cell (row 1
being the exception) and create the second column:
2170696969 2170696969
2021965 2172021965
2023241 2172023241
2225691 2172225691
2241710101 2241710101
2018501 2242018501
2050183 2242050183
2052903 2242052903

I have an Excel formula to convert raw data into a specific format.
Cell B1: =CONCATENATE(IF(LEN($A1)=10,LEFT($A1,3),LEFT(A1,3)),RIGHT($A1,7))
Cell B2..B65536:
=CONCATENATE(IF(LEN($A2)=10,LEFT($A2,3),LEFT(B1,3)),RIGHT($A2,7))
The row designations increments, so row 3 looks like this:
=CONCATENATE(IF(LEN($A3)=10,LEFT($A3,3),LEFT(B2,3)),RIGHT($A3,7))
.... all the the way down.

Excel does not have enough rows to handle the raw data, thus Access. The
Access table looks like:
RawData FinalNumber

FinalNumber should be of type text and look like:
2172021965

THANKS FOR YOUR HELP!
 
Hi,

I copy the data from a PDF file and paste it into Excel. After pasting it I
clean up the data, but the way I want to do it is simply past the data into a
table; pasting it into the table cleans up 95% of the data (I'll tackle the
5% of unwanted data after this this posted problem is resolved).

THANKS!
 
Back
Top