Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Hi,
I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Using Edit Replace. or the LEFT feature, etc.........you can either delete
the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Hi,
I don't want to delete the data in Table B, which is the master table. "CLR" wrote: Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Ok then, if you have lists in columns A and B and you wish to add "ST" to
each item in Column A, then in C1 put this formula and copy it down as far as you have data in column A..... ="ST"&A1 Then, highlight column C and do Copy PasteSpecial Values, pasting the entire column C back on itself....this will get rid of the formulas and just seave the STxxxxx. hth Vaya con Dios, Chuck, CABGx3 "JN" wrote in message ... Hi, I don't want to delete the data in Table B, which is the master table. "CLR" wrote: Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Hi Chuck - If he's adding ST to Table A items he needs to lose the leading
0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
Roger Ken, my bad, didn't see that......and my computer is messing up and
I'm not getting posts correctly and I didn't see this one of yours until after I posted my previous.....anyway, all we can do is give concepts without seeing the actual data....don't know how many of the A items have leading zeros and/or if any have two, etc etc.....of course "01A" can be replaced with "ST1A"......using Edit Replace feature..... Vaya con Dios, Chuck, CABGx3 "Ken Wright" wrote in message ... Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*------------ ---- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*------------ ---- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
:-)
"CLR" wrote in message ... Roger Ken, my bad, didn't see that......and my computer is messing up and I'm not getting posts correctly and I didn't see this one of yours until after I posted my previous.....anyway, all we can do is give concepts without seeing the actual data....don't know how many of the A items have leading zeros and/or if any have two, etc etc.....of course "01A" can be replaced with "ST1A"......using Edit Replace feature..... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/match text
I am not trying to add any characters here. I am trying to match up what in
Table A with the master data in Table B. The problem is that the data in Table B contains long string of text, while table A only contains a portion of this string. For instance, in Table A, I have "1A889"; then Table B has "ST01A889000, ST01A99900... etc." I want to find if Table B has this string "1A889". Thx. "Ken Wright" wrote: Hi Chuck - If he's adding ST to Table A items he needs to lose the leading 0s first, else they won't match. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "CLR" wrote in message ... Using Edit Replace. or the LEFT feature, etc.........you can either delete the ST from table B items, or add it to table A items...... hth Vaya con Dios, Chuck, CABGx3 "JN" wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up/math text
JN wrote: Hi, I am trying to match up a list of accounts (table A) from a huge text table (table B). The problem is table A doesn't have the same format as the text strings in table B. The text in table A contains part of the text strings in table B. For example, Table A has 01A0000 Table B has ST1A0000 Thx! =INDEX(ReturnRange,MATCH("*"&A2&"*",MatchRange,0)) where A2 houses the lookup value, that is, a value you want to look up. ReturnRange and MatchRange are ranges from Table B. The lookup vaue is compared with (matched against) MatchRange. ReturnRange is the range from which a corresponding value is returned. The lookup value must be a substring of some value in MatchRange for this formula to succeed. The leading 0 in 01A0000 would thwart a match against a value like ST1A0000 in Matchrange. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extra help with transpose | Excel Discussion (Misc queries) | |||
Shade cell according to text? | Excel Discussion (Misc queries) | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |