Is there any formula in excel if ex:Column B is HKG+MIA and I want to place HKG to C and MIA to D?

Answer 1

Highlight the values of Column B. Click "Data" from the main menu and select "Text to Columns". Pick Delimited for the original data type. Click Next>

For the Delimiters, pick Other and then put a + in the box just to the right of Other. Click Next>

Next to Destination on Step 3 of 3 click the right box with the red arrow and then click the cell to the right (Column C)of the first cell that you selected in Column B. Enter.

This should give you what you are looking for.

By the way....This will work with numerical sums as well as text strings.

Answer 2

You are not clear enough for a definitive answer. My best assumption is that you have data somewhere in two columns that share appropriate rows with column B's formulas (by that I mean that the HKG value in row 7 is the value you want used in B7). If so, the formula to use in C is: =HKG and in D: =MIA .

That is so whether you used the defined names feature and defined HKG and MIA appropriately or have enabled using labels in formulas.

Those formulas do not really do anything with column B. They simply look to the same place column B's formulas look.

If, however, you do not mean anything at all like that, but rather, you have a column of information in B all of which is in the form of HKG+MIA (three letters joined by a plus sign to three other letters), then you can extract them, minus the plus sign, into columns C and D using the following formulas:

C: =LEFT(B1,FIND("+",B1)-1)
D: =RIGHT(B1,LEN(B1)-FIND("+",B1))

which will actually take any length strings so long as you have them all in that form (string1+string2). So " ABCD+EFGHIJKLM " would put ABCD in column C and EFGHIJKLM in column D. However, it does get more complicated if you add plus signs and strings. By the way, there will be no errors generated if there are extras. You will simply get a value in C you are pleased with and the rest of the string in D. So D could look like: " DGT+JNY+WGY " if the string in B had four three letter strings and three plus signs.

Hope this is of some help. Feel free to email me if I have greatly misunderstood or was unclear. I might still be able to help.

Answer 3

Since you said you wanted a formula, give these two a try:

for column C: =LEFT(B7,FIND("+",B7)-1)
for column D: =RIGHT(B7,LEN(B7)-FIND("+",B7))

mike