Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelのテキストから最初、最後のX文字または特定の位置の文字を削除するにはどうすればよいですか?

ほとんどのExcelユーザーの場合、以下のスクリーンショットに示すように、テキスト文字列の先頭から最初のn文字を削除するか、テキスト文字列の末尾から最後のx文字を削除する必要がある場合があります。 この記事では、Excelでこのタスクをすばやく簡単に解決するための便利なトリックをいくつか紹介します。
 

docは最初のx文字を削除します1


方法1:数式を使用してテキスト文字列から最初または最後のx文字を削除する

 テキスト文字列の先頭から最初のx文字を削除します。

Excelでは、RIGHT関数とLEFT関数を使用して、文字列の最初または最後から特定の数の文字を削除できます。次のようにしてください。

1。 結果を入力する空白のセルC4に、次の数式を入力またはコピーします。

= RIGHT(A4、LEN(A4)-2)

を押し 入力します 最初の結果を得るためのキー。 スクリーンショットを参照してください:

docは最初のx文字を削除します2

ノート:上記の式では:

  • A4 文字を削除するセル値です。
  • 2 テキスト文字列の先頭から削除する文字数を意味します。

2。 次に、セルC4を選択し、この数式を適用するセルまで塗りつぶしハンドルをドラッグすると、最初の2文字がすべてテキスト文字列から削除されます。スクリーンショットを参照してください。

docは最初のx文字を削除します3


 テキスト文字列の末尾から最後のx文字を削除します。

最後の数文字を削除する必要がある場合は、LEFT関数をRIGHT関数と同じように使用できます。

この数式を空白のセルに入力またはコピーしてください。

= LEFT(A4、LEN(A4)-9)

次に、塗りつぶしハンドルをこの数式を適用するセルまでドラッグすると、最後の9文字がテキスト文字列から一度に削除されます。スクリーンショットを参照してください。

docは最初のx文字を削除します4

ノート:上記の式では:

  • A4 文字を削除するセル値です。
  • 9 テキスト文字列の末尾から削除する文字数を意味します。

方法2:ユーザー定義関数を使用してテキスト文字列から最初または最後のx文字を削除する

これは、テキスト文字列から最初または最後のn文字を削除するのにも役立つユーザー定義関数です。次のようにしてください。

 テキスト文字列の先頭から最初のx文字を削除します。

1。 を押し続けます Altキー+ F11 キーを押して Microsoft Visual Basic for Applications 窓。

2に設定します。 OK をクリックします。 インセット > モジュール、モジュールウィンドウに次のコードを貼り付けます。

Public Function removeFirstx(rng As String, cnt As Long)
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3。 次に、ワークシートに戻り、次の数式を入力します。 = removefirstx(A4,2) 空白のセルに入力し、塗りつぶしハンドルを下にドラッグして、必要に応じて結果を取得します。スクリーンショットを参照してください。

docは最初のx文字を削除します5

ノート:上記の式では:

  • A4 文字を削除するセルです。
  • 2 テキスト文字列の先頭から削除する文字数を示します。

 テキスト文字列の末尾から最後のx文字を削除します。

テキスト文字列から最後のn文字を削除するには、次のユーザー定義関数を適用してください。

Public Function removeLastx(rng As String, cnt As Long)
removeLastx = Left(rng, Len(rng) - cnt)
End Function

そして、この式を適用します。 = removelastx(A4,9) 必要に応じて結果を取得するには、スクリーンショットを参照してください。

docは最初のx文字を削除します6

ノート:上記の式では:

  • A4 文字を削除するセルです。
  • 9 テキスト文字列の末尾から削除する文字数を示します。

方法3:数式なしで最初、最後のx文字または特定の位置の文字を削除する

Excel関数を使用して特定の文字を削除することは、それほど直接的ではありません。 この方法で提供される方法を見てください。これは、マウスをXNUMX、XNUMX回クリックするだけです。 とともに 位置で削除 サードパーティのアドインのユーティリティ Kutools for Excel、テキスト文字列から最初、最後、または特定の文字を簡単に削除できます。 クリックしてKutoolsfor Excelをダウンロードしてください! 以下のデモをご覧ください。

インストールした後 Kutools for Excel、お申し込みください 位置で削除 これらの手順に従って:

1。 特定の文字を削除する範囲を選択します。 次に、をクリックします クツール > テキスト > 位置で削除。 スクリーンショットを参照してください:

2。 ポップアップで次の操作を指定します 位置で削除 ダイアログボックス。

  • (1.)削除する文字数を指定します。
  • (2.)選択 左から オプションの下に ポジション 最初のn文字を削除するセクション。スクリーンショットを参照してください。

docは最初のx文字を削除します8

ヒント:   位置で削除 ユーティリティは、最後のn文字または特定の文字を特定の位置から削除するのにも役立ちます。

方法4:式を使用してテキスト文字列から最初のx文字と最後のx文字の両方を削除する

両側のテキスト文字列から文字を削除したい場合があります。たとえば、最初の2文字と最後の9文字を同時に削除する必要があります。 ここで、MID関数はあなたに有利に働きます。

1。 この数式を空白のセルに入力またはコピーしてください。

= MID(A4,3、LEN(A4)-11)

を押し 入力します 最初の結果を得るためのキー。 スクリーンショットを参照してください:

docは最初のx文字を削除します11

ノート:上記の式では:

  • A4 文字を削除するセル値です。
  • 3 左側から削除する文字数よりXNUMXつ多いです。
  • 11 削除する文字の総数です。

2。 次に、セルC4を選択し、この数式を適用するセルまで塗りつぶしハンドルをドラッグすると、最初の2文字と最後の9文字がすべてテキスト文字列からすぐに削除されます。スクリーンショットを参照してください。

docは最初のx文字を削除します12



最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Velks · 1 years ago
    @NABEEL Use "text to column"
  • To post as a guest, your comment is unpublished.
    skyyang · 1 years ago
    @internet9135@gmail.com Hi, Giridhar,
    Maybe the below article can solve your problem, please try, thank you!
    https://www.extendoffice.com/documents/excel/829-excel-split-first-last-name.html
  • To post as a guest, your comment is unpublished.
    internet9135@gmail.com · 1 years ago
    Hi I have a data with names and surnames of some people, i want to separate surname in coloumn and name in another coloumn, surnames are not same like michel, john, daniel, williams like please help me to separate surname by using formula, I have lot of data
  • To post as a guest, your comment is unpublished.
    Jakub · 1 years ago
    If number or character differ you can use example below (I needed to remove the last character):
    =PART(A1;1;(LENGTH(A1)-1))
  • To post as a guest, your comment is unpublished.
    NABEEL · 2 years ago
    I WANT TO TRIM TEXT LENGTH, FOR EXAMPLE I HAVE SOME ADDRESS THAT LENGTH SOME COLUM 10 CHARECTOR AND SOME COLUM 20 CHARECTOR SOME 15 CHARECTOR IN THIS CASE I WANT TO TRIM WHICH COLUM TEXT MORE THAN 15 I WANT TO TRIM THAT COLUM TEXT TO 15 CHARECTOR, DONT TRIM OTHER COLUM TEXTS


    ANYBODY HELP ME
  • To post as a guest, your comment is unpublished.
    dezignextllc@gmail.com · 2 years ago
    Very helpful tip. Something we use a lot in our environment.
  • To post as a guest, your comment is unpublished.
    Serkan · 2 years ago
    I gave function error when I apply formula to cell. After 2 hours I try it again by using (;) instead of (,) and result positive. As result of;
    =removelastx(E4,3) (not working)
    =removelastx(E4;3) (working)
  • To post as a guest, your comment is unpublished.
    Biswanath · 2 years ago
    @Tumisang Use the formula =RIGHT(A2,LEN(A2)-11)
  • To post as a guest, your comment is unpublished.
    Sameer · 2 years ago
    How to remove the first character from a cell but not in every cell in that column? Thanks!
  • To post as a guest, your comment is unpublished.
    Randy Ralston · 2 years ago
    This helped to get the result from an entry but I am unable to LOOKUP the new value.
  • To post as a guest, your comment is unpublished.
    Mohammad Barzakh · 2 years ago
    This really helps me a lot in my daily working.

    Thanks
  • To post as a guest, your comment is unpublished.
    siena · 2 years ago
    Hi,
    I have the formula that gives me the #value error that I don't seem to be able to figure out how to fix it.

    Thank you in advance.
  • To post as a guest, your comment is unpublished.
    Jesus · 3 years ago
    How can I type the command to cut text from the left and the right?
  • To post as a guest, your comment is unpublished.
    wingstana@gmail.com · 3 years ago
    I can't make this formula work, I type : =LEFT(A1,LEN(A1)-4) and it gives error everytime.
    My A1 column has the following data: ELDB-AK-S-42228-001


    Why isn't this working?
  • To post as a guest, your comment is unpublished.
    baba · 3 years ago
    @HELPY try =RIGHT(A1, LEN(A1)-4) assuming characters before the first (-) are 3 digits long
  • To post as a guest, your comment is unpublished.
    garza01 · 3 years ago
    @Jeremy Try this, it solves exactly the way you want it.


    =IF((IFERROR((FIND(" ",A1,((FIND(" ",A1,1))+1)))/(FIND(" ",A1,((FIND(" ",A1,1))+1))),0))=1,LEFT(A1,((FIND(" ",A1,((FIND(" ",A1,1))+1)))-1)),A1)



    A1 is Taylor Jeremy D or without the D, as you wish
  • To post as a guest, your comment is unpublished.
    Jorge · 3 years ago
    @Jeremy Just for lettiing you know that I solved your problem, I hope im not too late.

    =SI((SIERREUR((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))/(TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)));0))=1;GAUCHE(R9;((TROUVE(" ";R9;((TROUVE(" ";R9;1))+1)))-1));R9)



    Cell R9 is your Taylor Jeremy D, try it with and without the D and you'll see it works fine
  • To post as a guest, your comment is unpublished.
    Alex B. · 3 years ago
    @Sigma Can also be accomplished with the below string, which is simpler and doesn't require formulas in multiple cells. Following same assumption that data is in A1 and the initial is always 1 character. TRIM function added within the LEN function in case there are unseen trailing spaces in the data. TRIM function added at the beginning of the formula will delete any residual spacing after the LEFT function is executed.

    =TRIM(LEFT(A1,LEN(TRIM(A1))-1))

    Or if you don't like the leading TRIM function you can subtract 2 characters from the LEN function instead of just the one. I prefer TRIM route in case there are more than 1 spaces in between the name and middle initial - have to account for inconsistent data entry.

    =LEFT(A1,LEN(TRIM(A1))-2)
  • To post as a guest, your comment is unpublished.
    Myke · 3 years ago
    i have a list of sizes that have numbers and names, words,etc. i want to remove all letters in the entire column leaving only the numbers. how do i remove all letter which come in hundreds of different variations and lengths?
  • To post as a guest, your comment is unpublished.
    Sigma · 3 years ago
    @Jeremy If the text name (Taylor Jeremy D) located in A1, you can use below function to acheive the result (remove the last name):
    =LEFT(A1,FIND(" ",A1,1))&LEFT(RIGHT(A1,LEN(A1)-A2),FIND(" ",RIGHT(A1,LEN(A1)-A2))-1)
  • To post as a guest, your comment is unpublished.
    shah · 3 years ago
    @Michael M. =mid(A1,4,len(a1))
  • To post as a guest, your comment is unpublished.
    shah · 3 years ago
    @SAMULA =year(date)
    you can get year
  • To post as a guest, your comment is unpublished.
    shah · 3 years ago
    @George use column to text in data section
  • To post as a guest, your comment is unpublished.
    Muhammad Awais Youna · 3 years ago
    Helpful, thanks
  • To post as a guest, your comment is unpublished.
    Jeremy · 3 years ago
    Hi there everyone,
    I am trying to remove an initial in a field that includes a persons name. An example is

    Taylor Jeremy D In this example I am trying to remove the D and just leave surname and first name there.

    The problem is that I have a heap of data which is inconsistently entered meaning that some of the fields have an initial (like that above) and some just say a name like Taylor Jeremy (without an initial).



    Any ideas so that I can have a consistent data set and one that excludes the initial?
  • To post as a guest, your comment is unpublished.
    Gaurav · 3 years ago
    @pejot Hey you can do this with a combination of functions:


    =CONCATENATE(LEFT(Y4;FIND("c";Y4)-1);RIGHT(Y4;LEN(Y4)-FIND("c";Y4)-1))



    Concatenate brings the two strings together, because you want to take a left side of the string and combine it with a right side of the string eliminating the middle values.
  • To post as a guest, your comment is unpublished.
    pejot · 3 years ago
    Hi there,
    im looking for a solution. Problem is that i have two colums of data where 1st column contain some text that part of is invalid and need to be removed, 2nd column contain invalid text string.


    Let's say cell A1 contain text string "ABCDEF"

    Cell A2 contain invalid part of the string, for example "CD"


    After removing invalid text string, cell A1 will look like this: "ABEF".

    How to do that? Thanks in advance!
  • To post as a guest, your comment is unpublished.
    G · 3 years ago
    remove all but the characters after a certain character. =RIGHT(G2,LEN(G2)-FIND("[",G2,4)). I was doing this for states so I had to convert the state into a unique character to reduce it down to 1 character rather then " NM ".
  • To post as a guest, your comment is unpublished.
    Tumisang · 3 years ago
    Good day



    Kindly assist in removing the letters which appear like that on numbers using excel







    26772309814Trudie



    Regards
  • To post as a guest, your comment is unpublished.
    chandran k · 3 years ago
    Hai

    Ex : AS SPLSH (CW) 100ml (112)

    hove to remove last (sum numbers)
  • To post as a guest, your comment is unpublished.
    Manohar Adsule · 3 years ago
    Worked for me. Thanks
  • To post as a guest, your comment is unpublished.
    Mohamed · 3 years ago
    [b]very nice tip[/b]
  • To post as a guest, your comment is unpublished.
    Pratik · 3 years ago
    @thilip You can use Find and Replace (Ctr + H). Find what: "*-" and Replace with: keep this field blank. Then click replace.
  • To post as a guest, your comment is unpublished.
    ankit · 3 years ago
    @ZEDD select the specific column and in "data" panel.. click "remove duplicates"
  • To post as a guest, your comment is unpublished.
    thilip · 3 years ago
    Please tell me how to get particular data in a cell
    Example
    Column A
    Ip-bs-bedroom
    Gh-ff-kitchen
    Ip-gh-main door
    .
    .
    Now I want in column B has only location ,that's bedroom, kitchen so I want to remove other except location
  • To post as a guest, your comment is unpublished.
    Manish Gupta · 4 years ago
    Dear Member,
    How do we find
    Data Required
    201 205
    2102 2105
    203 205
    2015 2015
    207 210
    2147 2150
    1000 1000
  • To post as a guest, your comment is unpublished.
    HELPY · 4 years ago
    Hi I want to remove everything after the first dash(-) and keep only the letters.
    How do I do that?
    example 123-ABC-BDER-AKD
  • To post as a guest, your comment is unpublished.
    Praveen Issac Shaji · 4 years ago
    Hi,

    I need support from Kutools team as i believe only you guys can help me. I have purchased a kutools license and couldn't find this feature in it.

    I would like to password protect/unprotect a 'sheet1' of 300 workbooks in one folder.
    For example: Only certain cells are editable in the password protected sheet1. You need to unprotect it with a password to edit all the cells.

    Scenario : When I open a workbook sheet1 opens and only certain cells are editable.(This is for on fields workers)
    For office purpose all the cells have to be editable and this is protected with a password which on field workers don't know.

    After some editing we have to protect back the sheet and save it.
    This happens for 300 workbooks' sheet1.
    So everytime

    [b]Opening workbook -> Review->Unprotect Sheet->Enter Password.[/b]
    After Changes
    [b]Review->Protect sheet->Enter Password->Save
    [/b]

    Can we do the above steps for a folder of workbooks using a VBA or any such help would be much appreciated.
  • To post as a guest, your comment is unpublished.
    Saif · 4 years ago
    I have data on browsers used by users where in I need to separate version type and names.
    Formula used by me is =LEFT(F7(MIN(FIND({0,1,2,3,4,5,6,7,8,9},F7&"0123456789")))-1). But its not working for all inputs. Data available is List of browser versions. Output needed is Group the browser versions. For example, Google Chrome 9.0, Google Chrome 9.1 and Google Chrome 9.3 should be grouped under “Google Chrome”. Version => Type: Google Chrome 9.1 => Google Chrome. Google Chrome 9.0 => Google Chrome. Google Chrome 8.0 => Google Chrome. Google Chrome 7.0 => Google Chrome. Google Chrome 6.0 => Google Chrome. Conditions are: Do not pivot the table. Need two columns – Version and Type. You can split the column but the catch is, few versions have numbers in the beginning. Eg: 360 Secure Browser
  • To post as a guest, your comment is unpublished.
    Maria · 4 years ago
    Hi!

    I Would like to removed selected characters on my excel workbook, how will i do that? is there any formulas for it?

    your immediate advice is highly appreciated.

    Thanks & BR,
  • To post as a guest, your comment is unpublished.
    Carlos · 4 years ago
    Hi.
    I would like to remove the last characters by using find and replace, but with Excel's Visual Basic (with commands).
    I would like to create a file that loads an existing file and creates a new one, with the column edited.

    Any help will be appreciated.

    Regards,
  • To post as a guest, your comment is unpublished.
    Xavier · 4 years ago
    @YODA Hi Yoda,

    just do a find and replace. You can easily acces the function using CTRL+F.
    You will want to look for "*" and replace with ""
    basically, wherever excel finds * it will replace it with a blank.
  • To post as a guest, your comment is unpublished.
    YODA · 4 years ago
    how can I mass delete a * in a excel spreadsheet description line?
  • To post as a guest, your comment is unpublished.
    N-tone · 4 years ago
    Thanks you for a very simple and a comprehensible approach. It works!!!! God bless you my dear Fellow-Anonymous!
    It took me 2 days of painful searches through Russian web and quite a number brain cells to find Excel Functions in Russian with explanatory notes provided, but all was in vain just because formulas refuse to work!! It seems like the accessibility and the total unwillingless to share common practices among my Russian colleagues, who just pretend they know *uck all about Microsoft products is still a big issue!!
    No, seriously, I owe u!
    Thanks!
  • To post as a guest, your comment is unpublished.
    narender · 4 years ago
    send the kutools for xp
  • To post as a guest, your comment is unpublished.
    David · 4 years ago
    @Shabi =LEFT(A1,SEARCH(" ",A1)-1) Should do the trick.
    Basically saying;
    I want text starting from the left of the string in A1, then i want to find the location of the first instance of a space and minus one from that. (otherwise your found string will contain the space).
    Simple enough, hope this helps.
  • To post as a guest, your comment is unpublished.
    Ramin Ramesh · 4 years ago
    hey tanx dears
    Replace All worked for me. hhhh very good trick..
  • To post as a guest, your comment is unpublished.
    SARPK · 4 years ago
    I want to delete the left 4 digit in whole sheet...

    for example 5895012032251........the result should be 012032251
    I can do the one only but how to do in whole worksheet..
  • To post as a guest, your comment is unpublished.
    Sachin Sawang · 4 years ago
    Hi Team,

    Need formula for removing first character from cell if it is special only.

    Kindly help...
  • To post as a guest, your comment is unpublished.
    Uma Kanth · 4 years ago
    @ZEDD Hi Zedd,

    For the task mentioned by you. We can use IF and COUNT IF combination Ex: =IF(COUNTIF($A$1:$A$275,A1)= 1,A1,FALSE)

    Regards,
    Umakanth Ramineedi