Preskoči na glavno vsebino

Kako najti položaj prve / zadnje številke v besedilnem nizu v Excelu?

Na primer, imate seznam besedilnega niza, ki je mešanica črk in številk. Če želite najti položaj prve ali zadnje številke v vsakem nizu, kaj bi storili? Pravzaprav vam bo uporaba formule pomagala hitro najti položaj prve / zadnje številke v določenem besedilnem nizu. V tej vadnici vam bomo pokazali podrobnosti reševanja te težave.

Poiščite položaj prve številke v besedilnem nizu v Excelu
Poiščite položaj zadnje številke v besedilnem nizu v Excelu


Poiščite položaj prve številke v besedilnem nizu v Excelu

Kot je prikazano na spodnji sliki zaslona, ​​za iskanje položajev prvih števil v besedilnih nizih storite naslednje.

1. Izberite celico B2, kopirajte in prilepite eno od spodnjih formul v vrstico s formulami:

1). Formula 1: = MIN (ISKANJE ({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789"));

2). Formula 2: =MATCH(TRUE,ABS(CODE(MID(A2,ROW($A$1:INDEX(A:A,LEN(A2))),1))-52.5)<5,0) + Ctrl + Shift + Vnesite;

3). Formula 3: =MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A2)),"",FIND({1;2;3;4;5;6;7;8;9;0},A2))).

2. Nato se v celici B2 prikaže položaj prve številke prvega niza. Zdaj povlecite ročico za polnjenje navzdol do celice B7, da zapolnite spodnji obseg.

Nato so navedeni vsi položaji prve številke celotnih nizov.


Poiščite položaj zadnje številke v besedilnem nizu v Excelu

Po iskanju položaja prve številke začnemo zdaj iskati položaj zadnje številke v nizih.

V tem razdelku sta za vas dve formuli.

Formula 1: = MAX (ČE (ŠTEVILO (VREDNOST (SREDINA (A2, VRST (POSREDNA ("1:" & LEN (A2))), 1))), VRST (POSREDNA ("1:" in LEN (A2)))) ) + Ctrl + Shift + Vnesite;

Formula 2: =MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A2,ROW(INDIRECT("1:"&LEN(A2)))),0)) + Ctrl + Shift + Vnesite

1. Izberite celico B2, kopirajte in prilepite eno od zgornjih formul v vrstico formul, nato hkrati pritisnite tipki Ctrl + Shift + Enter. Nato si lahko ogledate rezultate prikaza v B2.

2. Izberite B2, povlecite ročico za polnjenje navzdol do celice B7, da samodejno zapolnite obseg.

Potem boste takoj dobili položaje vseh zadnjih številk celotnega besedilnega niza.


Besedilo in številke iz ene celice v Excelu enostavno ločite v dva stolpca:

z Kutools za ExcelJe Razdeljene celice Pripomoček lahko vrsto celic razdelite na vrstice ali stolpce s pomočjo posebnega ločila, besedila in številk ali besedilo po dolžini certifikata. Prenesite in preizkusite zdaj! (30-dnevna brezplačna pot)


Sorodni članki:

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (15)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Ich habe gerade diese Lösung ausprobiert und es klappt bei mir nicht. Hat sich inzwischen etwas, was die Matrixformel betrifft, geändert?
This comment was minimized by the moderator on the site
Hi Wolfgang,

The formulas provided in this post work well in my case. What result did you get?
This comment was minimized by the moderator on the site
Thought I would let everyone know that I needed to find the last position of the house numbers in an address field in access. The only way I could do this was to export a short query to excel and run this formula. I know there is a way to get the excel functions in access but this was much easier. I had addresses that had varying house number lengths and then you throw in the 1st, 2nd, 3rd...St., Ave into the mix within the street name and you got a major problem parsing a very bad formatted address string.

address examples 1234 nw (or NW) 4th St.
12 West St North (or N)
123,456, and 789 Heritage Circle (or Crc)
123 & 456 N 1st. St

I figured out a way to parse off the first and second example real quick with some research (I would give credit, but right now I can't remember where I got the answer). It involved creating a VBA function to accomplish it. That worked great but I came to a problem when we come to the first "," or "and" or "&".

Using this formula in excel found the last number I needed without choosing the street number. Since most house numbers ended with a space " " between them and the direction segment or the street name segment, I added --- &" " after the find formula. Like this: in an array formula
=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0}&" ",E2,ROW(INDIRECT("1:"&LEN(E2)))),0))
A quick export back to an access table and wala! you have the number.
202 & 206 N Blanche Ave = 9

Now I know I could probably parse the rest of the address in excel but it actually worked real well in Access, so I just decided to do the rest there.

If there is a way to do it all in Access, I couldn't find it or figure it out.
This comment was minimized by the moderator on the site
goood, thanks
This comment was minimized by the moderator on the site
That's what I needed =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"));

thank you very much!
This comment was minimized by the moderator on the site
You are welcome O(∩_∩)O
This comment was minimized by the moderator on the site
how to get this to work in powerpivot
This comment was minimized by the moderator on the site
Hi,
Sorry can't help with this.
This comment was minimized by the moderator on the site
.... and here I thought that I knew Excel well. Bravo!!!!!
This comment was minimized by the moderator on the site
And here is a formula for finding the position of the last numeric character in a string, but WITHOUT using an array formula: =MAX(SEARCH(CHAR(9),SUBSTITUTE("0123456789"&A1,{0,1,2,3,4,5,6,7,8,9},CHAR(9),LEN(A1)+10-LEN(SUBSTITUTE("0123456789"&A1,{0,1,2,3,4,5,6,7,8,9},"")))))-10 The number 10 appearing in this formula, are due to the length of the constant string "0123456789", that is concatenated in this formula.
This comment was minimized by the moderator on the site
Many Thanks , Great
This comment was minimized by the moderator on the site
Hi George, your formula is may meet my requirement. I need similar formula to find out digit position in the string listed in "A" row in B row Required output 8 5 1 4 3 1 3 2 5 5 6 6 1 7 9 9 1 1 6 0 9 3 0 2 7 4 9 3 6 5 5 7 4 9 8 10 2 10 0 8 1 9
This comment was minimized by the moderator on the site
This was exactly what I wanted. Thank you.
This comment was minimized by the moderator on the site
The function 1). Formula 1: =MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")); It's absolutely what i needed and extremely cool! Thanks so much! Jon
This comment was minimized by the moderator on the site
Hats off mate..
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations