Extracting information from Column A to Column B Automatically

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
I am trying to automatically extract / copy information from Column A to Column B.
But the hard part is to automatically define what rows i want extract or copy from Column A.

ColA ColB
a
b
c
d
e
f
g_line
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
h_curve
i
j
f
l
m
I am importing a file into excel. So there might be even more than 100 of the numbers I want to copy over.
From (a to g_line) will always be constant - its the header of the imported file. But from h_curve to m is consistant -
its the bottom header of the file. So 1 - 10 (or 1 - 1000) I would want to copy or extract over to Column B.
Thanks for your help!!!!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
How about a "custom autofilter" on column A: Does not equal 0 (zero). Then copy/paste to column B
Another way might be to fill column "B" with a formula similar to the one shown below.
VBA code could also do the job.

Gary


Excel Workbook
AB
1ColAColB
2a 
3b 
4c 
5d 
6e 
7f 
8g_line 
911
1022
1133
1244
1355
1466
1577
1688
1799
181010
19h_curve 
20i 
21j 
22f 
23l 
24m 
Sheet1
 

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
This is what I am trying to do. The below code is the data imported into excel:

Code:
Sewers Systems
"Project Name = ",""
"SI Units? = ",#FALSE#
"Total No. Lines = ",3
"Starting HGL = ",0.00000000
"Return Period Index = ",2
"Min Cover = ",4.00000000
"Zero Min Cover at outfalls = ",#FALSE#
"Design Vel = ",3.00000000
"Min Slope = ",0.20000000
"Max Slope = ",10.00000000
"Min Pipe Size = ",12.00000000
"Max Pipe Size = ",96.00000000
"Curb Opening = ",""
LINE DATA =============================
"Network Name = ","PROPOSED STORM"
"Line No. = ",1
"Line ID = ","Pipe - (9)"
"Downstream Line No. = ",0
"X,Y Coord Dn = ",19.4154930243749,-146.2821744149114
"X,Y Coord Up = ",25.9215656661981,-31.2188056251907
"Deflection Angle = ",-86.76374854
"Inlet Time = ",5.00000000
"Line Length = ",115.24715970
"Invert Elev Dn = ",10.00000000
"Line Slope = ",0.00204952
"Invert Elev Up = ",10.23620091
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
"Line No. = ",2
"Line ID = ","Pipe - (11)"
"Downstream Line No. = ",1
"X,Y Coord Dn = ",25.9215656661981,-31.2188056251907
"X,Y Coord Up = ",83.7483229089266,-9.3996410159962
"Deflection Angle = ",66.09127805
"Inlet Time = ",5.00000000
"Line Length = ",61.80622782
"Invert Elev Dn = ",10.23600000
"Line Slope = ",0.00855612
"Invert Elev Up = ",10.76482172
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
"Line No. = ",3
"Line ID = ","Pipe - (17)"
"Downstream Line No. = ",2
"X,Y Coord Dn = ",83.7483229089266,-9.3996410159962
"X,Y Coord Up = ",155.9728320444629,-53.4800835952774
"Deflection Angle = ",52.06924185
"Inlet Time = ",5.00000000
"Line Length = ",84.61362265
"Invert Elev Dn = ",10.76500000
"Line Slope = ",0.00727805
"Invert Elev Up = ",11.38082246
"Rise = ",1.00000000
"Span = ",1.00000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"Line Part ID = ","0A967E34-111D-4B25-AD18-52826357B9D4"
"User def. Sta = ",0.0000000000000,0.0000000000000
User
"---------------------------------------"
                                                        0
"IDF Curves"
0,69.87033,0,79.25974,88.23514,102.6072,114.8193,127.1596
0,13.10001,0,14.60002,15.50002,16.50003,17.20003,17.80003
"sampleFHA.IDF"
#FALSE#,"",""
1,0,1,0
  END OF FILE
"End of file"

If I can do the above with this code that would be awesome.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977

ADVERTISEMENT

You lost me with your last post.

Can you show exactly what this data looks like (using Excel Jeanie) after import but before processing and then supply a second view of what it should look like after it is processed? You can get a copy of Excel Jeanie from the very top thread "Sticky" in this forum.

Gary
 

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
I wish I could install that but I can not because of admin priv. If you were to copy the code to a txt file. Then open via excel. Start a new excel blank file then drag the txt into the document. It should only fill up Column A.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977

ADVERTISEMENT

So the data is coming from a text file. You should try the "Import Wizard" instead of just dragging and dropping the text file into Excel. You'll have a lot more control over how it is formatted.

Google "Excel text import wizard" and have a look at the process for whatever version of Excel you are using. I used the wizard for XL2000 on the data you posted (comma delimited) and it did a pretty good job of columnizing it.

Gary
 

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
great!, when using the import wizard does it delete any of the contents ( commas, quotes, etc) or does it just columnize / delineates it into rows
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,197
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I wish I could install that but I can not because of admin priv. If you were to copy the code to a txt file. Then open via excel. Start a new excel blank file then drag the txt into the document. It should only fill up Column A.
Is your file a text file? If not, what is its extension?

As for your question... you told us the delimiters were "g_line" and "h_curve", but those words were no where to be found in your sample data. I am guessing the delimiters are "LINE DATA ====" and "IDF Curves", correct? Also, you said your data was numbers between them... that is not even close to correct. Anyway, maybe this formula placed in cell B2 will get you started. If not, you will need to tell us exactly what you are wanting from your data (we know you know, but you cannot expect us to know without you telling us).

=IF(AND(COUNTIF(A$1:A1,"LINE DATA*")>0,COUNTIF(A$1:A1,"*IDF Curve*")=0),MID(A2,FIND("= "",",A2&"= "",")+4,99),"")
 

Forum statistics

Threads
1,137,292
Messages
5,680,645
Members
419,923
Latest member
Kalthus

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