Question about Business & Productivity Software

1 Answer

I have a question; How do I seperate address written in one column into 4 column as line1 line 2 citi pincode

Posted by on

×

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Mayor:

    An expert whose answer got voted for 2 times.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 5 Answers

Assuming 1) you're using Excel and 2) the addresses are written:

1234 Main St., Anytown, CA 90210

You can use "Text to Columns" feature (selected "Delimited" and check the box next to "comma"). This will split the data into 3 columns (address | city | state and zip). Then run the "Text to Columns" again on the "state and zip" column and use "Fixed width" to separate the state from the zip.

If the assumptions I used are incorrect, clarify and we'll try again.

Posted on Jan 06, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

When i copy a sum formula in excel the first cell does not remain the same it increments by 1. how can I prevent this?


There are 2 types of direct cell references that you can use when you're writing formulas: Relative References & Absolute References.
A Relative Reference is the address of a cell (e.g. A5). When a Relative Reference in a formula is copied from one cell to another, the Reference gets changed automatically. e.g. If you put a formula in cell c5 as A5+1, when you copy this from c5 to c6 the formula A5+1 will automatically change to A6+1.
An Absolute Cell Reference does not change when its copied to another location. As in the example above if the formula in cell C5 is written as $A$5+1, if you copy this formula from C5 to C6 it will remain as $A$5+1 (NOT change to $A$6+1.
The $ sign signifies Absolute, and can be applied to the Row reference, the Column reference, or both Column & Row (as in the example).

Oct 17, 2014 | Microsoft Excel for PC

1 Answer

How can I use a relative address in the @find function? It changes to a fixed address when I stop editing.


When you include addresses like A2, C10, F15 they are relative addresses .eg: If you have entries like:
in B4 you have saturday
in C6 you have the formula =FIND("a",B4) .....the answer is 2
B4 is a relative cell address, because if you copy the formula across the row, OR up/down the column, the formula changes and the answer changes
To make it an absolute address you have to type a $ in the cell address. If you type the cell address formula as $B4, it means wherever you move to or copy the formula the B will not change
If you type B$4 the row no 4 is ABSOLUTE or fixed.
$B$4, both column B and row 4 are fixed. If you move the formula anywhere the cell reference will not change.
Summary B4 is a relative cell address
$B4 is absolute address (B is fixed)
B$4 is absolute address (4 is fixed)
$B$4 is abso;lute address (Bis fixed, 4 is also fixed)
Hope this answers your question
luciana44

Sep 05, 2009 | Corel WordPerfect Office X4 Home & Student...

1 Answer

Finding the correct row num


You would have to combine the use of 2 functions. The Address and Match funbctions.
Lets say the number you want the address of is located in cell F1 and you have 2 columns of numbers. One colum in Column A and the other in column B. I will give you 2 formulas. The 1st one will return just the row number. The 2nd one will return the cell address.

Option 1: Lets say you just want to know the row reference of the number in cell F1. Place this formula in cell D1. =MATCH(F1,A1:A20)
If you have another column ytou want the row number of, place the formula in lets say cell D2 and change the column references from 'A' to 'B'.

Option 2: If you want the cell reference, place this formula in cell D1 and D2 instead of the firt formula.
=ADDRESS(MATCH(F1,A1:A20,0),1,1,TRUE)
And just like the first option, for the 2nd column, put the formula in D2 and change the column reference 'A' to 'B'.

Feb 17, 2009 | Microsoft Excel for PC

1 Answer

Creating vacation accrual spreadsheet


Here is one way to do it:
1. In column A enter the name
2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)
3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)
4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)
5. In column E, put this formula:
(D2/30)*1.25 (assuming this is line 2)
You can then copy these lines as many times as you need to, total them, etc.

Dec 27, 2008 | Microsoft Office Standard for PC

1 Answer

BASIC EXCEL FORMULA


Are you looking to solve any particular problem?--- because there are a huge number of possible formulas in Excel.

However, in my opinion, the most commonly needed ones are addition, subtraction, division, multiplication, and summing.

Suppose you have the following numbers typed into your Excel spreadsheet:

columns: A B C D
rows
1 20 3
2 10 4
3 15 2
4 1 2 3


Then suppose you type in the following formulas (in the D column):

columns: A B C D
rows
1 20 3 =A1+B1
2 10 4 =A2-B2
3 15 2 =A3*B2
4 1 2 3 =sum(A4:C4)


Then the following answers will appear in the D column:

columns: A B C D
rows
1 20 3 23
2 10 4 6
3 15 2 30
4 1 2 3 6

Sep 29, 2008 | Microsoft Business & Productivity Software

1 Answer

CSV file to Import correctly into Excel 2003


Rudils,

The key is to import the data and not open the file directly.

1. Open a Blank Workbook in Excel.
2. Data, Get External Data, Import Data. (Excel 2007 is Data, Get External Data, Data from Text)
3. Browse to your .csv file and Select "Import".
4. Import Wizard should appear.
5. Page 1 Select "Delimited"
6. Select the row which you want to start the import.
7. click "Next"
8. In the Delimiters, select "semicolon" and/or other delimiters you are using.
Note: The bottom half of the window will preview the way the data is to be imported.
9. click "Next"
10. highlight each column of your data in the window below. For each column you can specify "General", "Text", "Data", or "do not import column" using the radio buttons in the top left of the Wizard box. This is an optional step.
11. Click Finish.

I hope that helps. Please add a comment if it not clear.

kpenguin

Nov 22, 2007 | Microsoft Excel for PC

1 Answer

Ms excel


Vlookup: =VLOOKUP(A1,A1:C10,3,FALSE) 1. Lookup_value - A1 the value you want to search. 2. Table_Array - A1:C10 You will only search the lookup_value in the left column which is A1:A10 in this example. 3. Col_Index_Number - If you find a matching value give me column 3. Since A1=A1 it will give me C1. In this example 1 is A, 2 is B and 3 is C. 4. Range_lookup - I always choose false which is exact match. True will give you approximate match and its not always correct. Vlookup is used when you have a list of values and you want additional values that exist in other fields. You will get those values only for the fields that you search for. In your example you can get the address by running vlookup at the names, it is also good way to search duplicates. Q2 is very simple, on field D1 just type: =A1 & " " & B1 & " " & C1 Just drag it or double click on the drag square Let me know if you have any other questions Daniel

Sep 24, 2007 | Microsoft Office Standard for PC

1 Answer

Merging Data in 2 Separate Excel Workbooks


If the First Name and Last name are equal to both work sheets, I will just sort them by First Name and Last name and copy the results into a separate work sheet. If the names are different you will have to use Access: This can be easily done with Access and SQL, just import both work books into different tables to access and use the create query wizard, play with the results until you will get the result you want. Access will also solve the duplicate problem easily. Very important: You will have to use join properties: Read help about join properties and understand how this works... Afterward you can export it back to Excel or use Copy/Paste. If you can't use Access and have only Excel: You will have to use Vlookup on various fields until you will get the result you want. Its a hard work but in the end you will get the same result as access. Let me know what happend Daniel

Aug 20, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Business & Productivity Software Logo

Related Topics:

67 people viewed this question

Ask a Question

Usually answered in minutes!

Top Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18297 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...